Posts by NBVC

You can use ROW(A6)*-1 copied down (assuming starting at -6) or ROWS($A$1:$A6)*-1

See attached.

I double-checked and it looks correct.

Update your formula in Sheet2, H7 to: =IFERROR(VLOOKUP(B7,Sheet3!A1:F41837,3,0),"") copied down.

Then use: =IFERROR(LARGE(IF(Sheet2!$H$7:$H$206="CA",Sheet2!$C$7:$C$206),ROWS($A$1:$A1)),"") in Sheet1

Looks like you have some #N/A errors in column H...

Try: =IFERROR(LARGE(IF(NOT(ISNA(Sheet2!$H$7:$H$206="CA")),Sheet2!$C$7:$C$206),ROWS($A$1:$A1)),"")

confirmed with CSE keys, copied down.

Or use the IFERROR() in the VLOOKUP in column H to return a 0 instead of error, then use my initial formula

The ROWS() function is used to get a consecutively increasing number starting from 1 (for largest), 2 (for second largest), etc...

Did you confirm the formula with CTRL+SHIFT+ENTER keys all hit at the same time? This should show { } brackets around the formula. Then copy it down.

Try something like this:

=IFERROR(LARGE(IF(Sheet1!$H$2:$H$100="CA",Sheet1!$C$2:$C$100),ROWS($A$1:$A1)),"")

Confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down.

Adjust ranges as necessary.

You can try something like this:

Assuming your items start in E6 and go rightward to H6... then in your new sheet try something like:

=INDEX(Sheet1!$E$6:$H$6,ROWS(Sheet1!A$1:A1))

copied down.

Adjust as necessary.

Check your P range. It needs to be consistent with the F range.

We are not supposed to work with email outside forum. Try saving ad .XLSX and try using attachment tool

Don't see any attachment.

You also have to adjust the ranges to suit your data

Did you confirm it with CTRL+SHIFT+ENTER?

If still can't get it then attach it to this thread

You can add more conditions to the formula...

=MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100<30,$P$2:$P$100)))

=MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100>=30,IF($P$2:$P$100<60,$P$2:$P$100))))

etc.

Does this work?

=INDEX('Annual Totals'!B8:ED10,SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(ROW('Annual Totals'!B8:ED10)-7)),SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(COLUMN('Annual Totals'!B8:ED10)-1))+1)

So the result of the nested HLOOKUP could be in any cell withing B8:ED10 or is it in a specific column only?

You'll have to use an array formula (confirmed with CTRL+SHIFT+ENTER, not just ENTER).

e.g.

=MEDIAN(IF(MONTH($A$2:$A$100)=10,$B$2:$B$100))

where A2:A100 contain dates (formula above looks for 10th month. i.e. October), and B2:B100 contain values to find median of

What's the current HLookup formula?

If you use index Match, you can add +1 to the Match portion of the formula to adjust to next column...

e.g. =INDEX(B8:ED10,MATCH(A1,B8:B10,0),MATCH(A2,E8:ED10,0)+1)

Try:

=[@Mean]-(SUM(SUMIFS([Mean],[35S],[@35S],[v.Con],"NSB",[D1],IF([@D1]="","",[@D1]))))

I have been made aware that you have also cross posted this question on another forum. Please indicate by adding the link here. We can't help you otherwise as it is against the rules.