# Posts by NBVC

• ## Finding the Median for certain dates from a list of data

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

• ## Finding the Median for certain dates from a list of data

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

• ## Finding the Median for certain dates from a list of data

Don't see any attachment.

• ## Finding the Median for certain dates from a list of data

You also have to adjust the ranges to suit your data

• ## Finding the Median for certain dates from a list of data

Did you confirm it with CTRL+SHIFT+ENTER?

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

• ## Finding the Median for certain dates from a list of data

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.

• ## Vertical lookup when the range changes.

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)

• ## Vertical lookup when the range changes.

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

• ## Finding the Median for certain dates from a list of data

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

• ## Vertical lookup when the range changes.

What's the current HLookup formula?

• ## Vertical lookup when the range changes.

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)

• ## Matching blank cells in SUMIFs with multiple criteria

Try:

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

• ## Extract data fields from wrapped text cell

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.

• ## Extract data fields from wrapped text cell

Can you give real examples to see if there is some one of distinguishing the field header from the data?

• ## Extract data fields from wrapped text cell

What is between Data1 and Field2? Is there anything of consistency that can be used to separate the fields?

• ## Split of the address string into separate lines as per defined structure

We've been advised that you are still cross posting on other forums.

If you want help on this, and any other forum, as they all have the same rules, then please comply to the rules of cross-posting.

• ## 3 Random Values from a list skipping blanks

Create a couple of helper columns adjacent to the main list.

These columns can be hidden.

In F4: =IF(E4="","",RAND()) copied down the full 100 rows

In G4: =IFERROR(RANK(F4,\$F\$4:\$F\$104),"") copied down the full 100 rows.

Now, in I4: =INDEX(\$C\$4:\$C\$104,MATCH(ROWS(I\$4:I4),\$G\$4:\$G\$104,0)) copied down 3 rows.

Adjust formulas to suit your actual data ranges.

• ## is it possible to use an inputted cell value as the number of the starting row in a range?

Assuming you enter that value in B1, then try:

=COUNTIF(INDEX(A2:A5000,B1-1):A5000,">12")

• ## Count Unique Records with Multiple Criteria

Try:

=SUMPRODUCT((Data!\$A\$1:\$A\$11>=\$B\$1)*(Data!\$A\$1:\$A\$11<=\$B\$2)*(Data!\$B\$1:\$B\$11=B\$4)*(Data!\$C\$1:\$C\$11=\$A5)*(Data!\$D\$1:\$D\$11<>0)/(COUNTIFS(Data!\$A\$1:\$A\$11,Data!\$A\$1:\$A\$11&"",Data!\$B\$1:\$B\$11,Data!\$B\$1:\$B\$11&"",Data!\$C\$1:\$C\$11,Data!\$C\$1:\$C\$11&"")))

• ## Count Unique Records with Multiple Criteria

Try this formula in Output sheet, B5:

=SUMPRODUCT((Data!\$A\$2:\$A\$11>=\$B\$1)*(Data!\$A\$2:\$A\$11<=\$B\$2)*(Data!\$B\$2:\$B\$11=B\$4)*(Data!\$C\$2:\$C\$11=\$A5)*(Data!\$D\$2:\$D\$11<>0)/(COUNTIFS(Data!\$A\$2:\$A\$11,Data!\$A\$2:\$A\$11&"",Data!\$B\$2:\$B\$11,Data!\$B\$2:\$B\$11&"",Data!\$C\$2:\$C\$11,Data!\$C\$2:\$C\$11,Data!\$D\$2:\$D\$11,Data!\$D\$2:\$D\$11&"")))

Copied down and across.