Median if returning "#NUM"

[Lifeseeker]

Hi there,

I am experiencing some difficulties with a simple median if function in Excel. I have attached the worksheet.

In "mediansnapsholos report matrix" worksheet, you see a table with few numbers. B3 is where the formula begins.

Please Login or Register  to view this content.

I have also used control_shift_enter, but I still get 0s. I have done this before, but seem to be stuck at this one.

Is anyone able to assist?

Thanks

[CheshireCat]

Your range 'MedianSnapshotLOS Report Matrix'B2:M2 is formatted as text. It is invalidating the comparison to YTDraw!$A$2:$A$995.

[Lifeseeker]

Hi,

still not working. Columns with numbers are formatted as "number" now...

Please Login or Register  to view this content.

any other ideas?

[CheshireCat]

I still see issues with that range, try re-entering the values or copy-paste-add a zero to get the numbers to be recognized.

[shg]

1. The values in row 2 are still text. You can convert them to numbers by copying an empty cell, and doing paste special, and tick Add.

2. You've entered the formula as a single array formula throughout the 36 cells. It should be one array formula in B2 copied throughout the range:

=IFERROR(MEDIAN(IF((YTDraw!$C$2:$C$995='MedianSnapshotLOS Report Matrix'!$A3)*(YTDraw!$A$2:$A$995='MedianSnapshotLOS Report Matrix'!B$2), YTDraw!$E$2:$E$995)),0)

Please Login or Register  to view this content.

3. You do yourself a disservice centering all your data and minimizing the column widths. If you restore Excel's default horizontal alignment ("General"), numbers align right and text aligns left, which would cue the source of the first problem.

[daddylonglegs]

You could also do the conversion within the formula with a strategically placed +0.....

Try this formula in B3

=IFERROR(MEDIAN(IF((YTDraw!$C$2:$C$995=$A3)*(YTDraw!$A$2:$A$995=B$2+0),YTDraw!$E$2:$E$995)),0)

confirm with CTRL+SHIFT+ENTER and then copy across and down

[olasa]

Or...
Sheet: MedianSnapshotLOS Report Matrix
Range: B2:M2 Format as General, Retype the numbers
//Ola

[Lifeseeker]

Hi,

I am still struggling with it.

Please Login or Register  to view this content.

I put this formula in B2 and pressed control + shift+ enter. I still get 0.

any other ideas? I must be missing something.

[JosephP]

column references are wrong-you can't get a median of text strings. formula should be
=IFERROR(MEDIAN(IF((YTDraw!$C$2:$C$995=$A2)*(YTDraw!$A$2:$A$995=B$1),YTDraw!$D$2:$D$995)),0)
with cse.

[Lifeseeker]

Ahh, problem solved!

THanks