I have a range of 10 numbers, in a row not a column, and I want to find where a eleventh number falls in that range. A value of 1, meaning greater than all 10 , or 11 meaning less than all 10, or somewhere in the middle say 5th or 7th largest value. Values are not in order ascending or descending.
Please upload an ODF type sample file here.
Hallo
=AND(B1>=MIN($A$1:$L$1) ; B1<=MAX($A$1:$L$1))
To expect such an answer would require to assume that the eleventh number is one of the already known ten numbers.
Please be more precise about what you have and what you want.
If you have 10 arbitrary numbers in A1:A10, and another arbitrary number in B1, the formula =SUMPRODUCT(B1>A1:A10) will tell you for how many numbers of the sample the additional number is GREATER. For variants simply vary the formula.
1 Like
That formula only tells me if it is in the range, it doesn’t tell me if it is the 4th largest number or the 7th.
Lupp, that is exactly what I was looking for, except I changed the > to a < as I wanted to know how many in the sample were greater. I also added 1 as the current number is not part of the sample to determine its place in the eleven values. A little background, I have Solar panels and 10 years of production values by month. I wanted to know where this year’s production fell in the last 10 years range. The sample table is posted below:
| 2023 | 2022 | 2021 | 2020 | 2019 | 2018 | 2017 | 2016 | 2015 | 2014 |
|---|---|---|---|---|---|---|---|---|---|
| 923 | 913 | 975 | 996 | 931 | 1,002 | 978 | 802 | 941 | 871 |
| 867 | 969 | 1,016 | 1,053 | 986 | 942 | 960 | 1,008 | Inverter Issue | 955 |
| 981 | 824 | 887 | 924 | 927 | 945 | 998 | 1,000 | 856 | |
| 810 | 824 | 758 | 851 | 586 | 850 | 771 | 780 | 818 | 918 |
| 666 | 636 | 647 | 621 | 670 | 706 | New Roof & Inverter | 792 | 700 | 727 |
| 632 | 645 | 612 | 654 | 533 | 657 | 682 | 559 | 500 | 636 |
| 499 | 460 | 441 | 434 | 452 | 469 | 467 | 403 | 447 | 384 |
| 374 | 437 | 552 | 534 | 488 | 637 | 336 | 607 | 536 | 404 |
| 641 | 672 | 405 | 607 | 602 | 510 | 671 | 551 | 741 | 607 |
| 838 | 828 | 923 | 781 | 900 | 816 | 885 | 908 | 741 | 753 |
| 864 | 892 | 894 | 856 | 947 | 859 | 860 | 945 | 946 | 974 |
| 999 | 883 | 919 | 967 | 881 | 804 | 857 | 775 | 1,028 | 917 |
Thank you so much, you were a great help.
Suppose your data is in the range of cells A1:J1, and the 11th number is in cell N1.
Use the formula:
=RANK(N1; A1:J1~N1; 1)
See also RANK, RANK.AVG, RANK.EQ
1 Like
Can I ask what is the purpose of the ~N1?
~ (Tidle) - range union in Calc.
If you wanted to determine the Rank of cell C1 in the range A1:J1, then the formula would be used:
=RANK(C1; A1:J1; 1)
Note that Excel formulas use other Reference operators…
I appreciate the information and your effort to educate me, even though I don’t understand how it could be helpful. I get the same result including the ~N or not. Just to show you I’m not a complete idiot here is my final RANK formula: =RANK(G20,INDIRECT(“O”&(MATCH(EDATE(EOMONTH(A20,0),-1)+1,A4:A15)+3)&":X"&(MATCH(EDATE(EOMONTH(A20,0),-1)+1,A4:A15)+3)),0). G20 is the current estimate for this month, while A20 is the current date. EDATE(EOMONTH(A20,0),-1)+1, sets the current date to the first of the month to match the date in the yearly table. MATCH(EDATE(EOMONTH(A20,0),-1)+1,A4:A15)+3, determines the row number of the current month, of the spreadsheet, not the row of the table. The INDIRECT formula concatenates the row with the column range. Who knows, maybe this shows how much of an idiot I am as I may be over complicating this. I know I could put some of these formulas in cells and just refer to them, but I didn’t want to have o jump all over later to see what I did.
In offering the formula, I have interpreted your words as follows.
Cells A1:J1 - a range of 10 numbers, cell N1 - the 11th number.
To determine the rank, we must join the 11th number (N1) to the array of numbers A1:J1 (this requires ~), and calculate the RANK function.
1 Like
I don’t.
2 Likes