Min/Max with IF(?) criteria

[Guest]

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.

 

[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)

 

[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

 

[Guest]

Thanks Bernie. Works perfectly.

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

 

[Guest]

Bob,

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

 

[Sandy Mann]

I believe that Bob may have a typo, does:

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

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk