Min/Max with IF(?) criteria

R

ronnomad

I have two columns. A contains numbers, B contains text days of week (Mon,
Tue, etc.) starting from 01/01/07. How do I write a formula that will give
me the Min in column A based on the day of the week, discounting any days
where the the number in A is zero (presumably the same formula will work for
Max).

Thanks,

Ron R.
 
B

Bob Phillips

=MIN(IF(A1:A100<>0)*(B1:B100="Mon"),A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bernie Deitrick

Ron,

Array enter formulas like this, using Ctrl-Shift-Enter:

=MIN(IF(TEXT(A2:A100,"ddd")="Tue",IF(B2:B100<>0,B2:B100)))
=MAX(IF(TEXT(A2:A100,"ddd")="Tue",IF(B2:B100<>0,B2:B100)))

You could also use WEEKDAY, but I find this easier to understand....

HTH,
Bernie
MS Excel MVP
 
R

ronnomad

Thanks Bernie. Works perfectly.

Bernie Deitrick said:
Ron,

Array enter formulas like this, using Ctrl-Shift-Enter:

=MIN(IF(TEXT(A2:A100,"ddd")="Tue",IF(B2:B100<>0,B2:B100)))
=MAX(IF(TEXT(A2:A100,"ddd")="Tue",IF(B2:B100<>0,B2:B100)))

You could also use WEEKDAY, but I find this easier to understand....

HTH,
Bernie
MS Excel MVP
Click to expand...
 
R

ronnomad

Bob,

Tried the formula but kept getting a formula error message pointing to the
zero. Any suggestions?
 
Top