MAX/MIN/Median of column depending on neighboring values

[Rydell]

I don't know what exactly I am doing wrong but I have tried many different ideas and I am not sure what to do. What I want done is to be able to take the Max/Min/Median of a column but only depending on certain values of the neighboring column's cell.

For example

A B
23 0
15 7
19 4
59 0
10 0
75 8

How can I have excel find the MAX value in A depending on if the neighboring B value is above 0? So it will essentially ignore any A value if the B value is 0. I know it can be done because I can just do an IF statement for each cell but I do not know how to combine the IF statement to work properly within another function. I assume once I figure it out I can easily apply it to other formulas (Min/Median/Percentile) and such. Any help would be greatly appreciated.

[ratcat]

G'day Rydell and welcome to the forum,

Being unsure of the required result if the neighbour cell wasn't zero but try this for starters placed in cell C1

=IF(B1=0,"","Insert formula here")

If you need further help upload example of your workbook (sensitive info excluded) with a before and after results displayed in the workbook.

Cheers

RC

[DonkeyOte]

Perhaps:

=MAX(IF(B1:B6>0,A1:A6))
confirmed with CTRL + SHIFT + ENTER

(same principles can be applied to MIN/MEDIAN by simply altering function name - remember to set the Array each time (Ctrl + Shift + Enter))

[Rydell]

Thanks for the help, I was able to get it working for the max value, it wouldn't work for other functions for some reason. I realized that even with the false statement as 0, it would count it as zero instead of ignoring the cell. I just made the false statement be text and the formulas seem to be working.

[DonkeyOte]

I realized that even with the false statement as 0, it would count it as zero instead of ignoring the cell.

Not so. Given no FALSE value is assigned to the IF a default Boolean False will be added to the Array of values.
In this context given the Booleans are not constants they will not be coerced (ie they will not be treated as 0) - they will simply be ignored by MIN/MAX etc...

So to reiterate:

=MAX(IF(B1:B6>0,A1:A6))

=MIN(IF(B1:B6>0,A1:A6))

=MEDIAN(IF(B1:B6>0,A1:A6))

would all work as expected if correctly entered (& committed as Arrays via CTRL + SHIFT + ENTER)