Excel 2007 : STDEV, MIN, QUARTILE, MEDIAN, MAX with IFS

[dip11]

I'd like to run a number of formulae on a range of cells in column O which have values between 0 to 3000 but may also have blanks.
Additional criteria are located in column BB, P and cell A1

Based on the above, I would like to know how I can create "IFS" versions of the following formulae:
STDEV
MIN
QUARTILE
MEDIAN
MAX

Thanks

[martindwilson]

you could change that to
=IF(AE1<>"",AE1+1440,0)
minif and maxif here
http://www.dailydoseofexcel.com/arch...inif-examples/
and here
http://blog.contextures.com/archives...x-if-in-excel/

[dip11]

Cheers, sorry for editing the thread nearly completely while you were replying. I decided to revise my formula to fix the issue.

For min/max if, I note the DDOE site has multiple conditions too so that should take care of the IFS issue.

Is is possible to do the same for STDEV and QUARTILES/MEDIAN?

[dip11]

OK so I tried the MIN with CSE and it doesn't work properly.
{=MIN(IF((Sheet1!$BB:$BB>$A$1-12)*(Sheet1!$P:$P=B$1),Sheet1!$O:$O))}

It gives me a value of 0 (and for the other columns values from 0 to 5) even when there isn't even a value of 0 in the column for these criteria.
I can't exclude all 0 values however as some column may well have 0 as the min while others don't

Changing the range from $O:$O to $O2:$O14299 results in all the min values turning to 0. Even stranger

Strangely, MAX seems to work

[martindwilson]

these are the results i get from different scenarios

[dip11]

Interesting.
You get a 0 for MIN as well even though there is no 0, but a blank.

I've tried it with an additional condition of $O:$O>=0 but that didn't change anything.

Is there a way of forcing it to count actual numbers only?

[martindwilson]

did you not look at example
=MIN(IF(($J$1:$J$1000>10)*($K$1:$K$1000=I3)*($L$1:$L$1000<>""),$L$1:$L$1000)) which ignores blanks?