I have a table that keeps scores from fantasy football league. The lowest scoring week is not included in the total.
I'm able to find the lowest score using the =min function [=min(B2:R2)]
I can find the first instance using the =match function. [=MATCH(S2,B2:R2,0)]
However I would like a formula that returns the latest occurrence of the lowest scoring week.
For example Portia lowest score is 8 the first occurrence is week 4 but I would like it to return the latest occurrence which is week 11
The easiest way to find the last cell with value in a row is using keyboard command. Click on the first cell of the row and press CTRL+ Right Arrow Key. Your cursor will move to the last non-empty cell in that row.
Locate the last cell that contains data or formatting on a worksheet. To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press CTRL+END.
The Lookup Wizard helps you find other values in a row when you know the value in one column, and vice versa. The Lookup Wizard uses INDEX and MATCH in the formulas that it creates. Click a cell in the range. On the Formulas tab, in the Solutions group, click Lookup.
You can also do this using LOOKUP
function and avoiding array formulas, e.g. assuming S2
has the minimum value you can get the last week with that value with this formula in T2
copied down
=LOOKUP(2,1/(B2:R2=S2),B$1:R$1)
This works because (B2:R2=S2)
returns an array of TRUE/FALSE
values, and then dividing 1 by that array gives you an array of 1
s or #DIV/0!
errors. When you use 2
as the lookup value on that array it won't find any 2
s so will therefore match with the last number (the last 1) in the array. That corresponds to the last week with your smallest value so using the top row as the "return vector" means that the formula returns the corresponding week number as required
You will need to use an Array Formula to achieve this. The following will return the "highest" lowest value if you are using columns B:R as the data columns and column T as the Low Week column. You need to enter this as an array (CSE) formula by copying and pasting it into cell T2 and pressing Ctrl+Shift+Enter.
=IFERROR(INDEX($A$1:$R$1,1,SMALL(IF(B2:R2=MIN(B2:R2),COLUMN(B2:R2),FALSE),COUNTIF(B2:R2,MIN(B2:R2)))),"")
First, the COUNTIF(B2:R2,MIN(B2:R2))
will count the number of times the minimum value occurs. This is needed because a simpleMIN(B2:R2)
will only grab the first minimum value, not the nth minimum value.
Next, the IF(B2:R2=MIN(B2:R2),COLUMN(B2:R2),FALSE)
is the actual array formula part of the equation (the reason why you need to use Ctrl+Shift+Enter). It will only return the references for when you have a minimum and returning FALSE
for all other entries greater than the minimum. Now, you have an array of only the column numbers the smallest values in the dataset.
Then, the SMALL(IF(B2:R2=MIN(B2:R2),COLUMN(B2:R2),FALSE),COUNTIF(B2:R2,MIN(B2:R2)))
will take the column numbers of the smallest values and find the latest occurrence of this, which comes from the COUNTIF(B2:R2,MIN(B2:R2))
code.
Now that the last smallest occurrences' column number is know, you can use the INDEX
function to find the value in the first row INDEX($A$1:$R$1,1
.
Finally, the IFERROR
will display the Low Week row if the SMALL
function finds a match, otherwise it will display a blank cell. This could be used to copy this array formula further down the page for rows that you don't yet have users for.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With