Min/Max with IF(?) criteria
 

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.

Min/Max with IF(?) criteria
 

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



"ronnomad" wrote in message
...
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.

Min/Max with IF(?) criteria
 

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


"ronnomad" wrote in message
...
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.

Min/Max with IF(?) criteria
 

Thanks Bernie. Works perfectly.

"Bernie Deitrick" wrote:

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


"ronnomad" wrote in message
...
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.

Min/Max with IF(?) criteria
 

Bob,

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

"Bob Phillips" wrote:

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



"ronnomad" wrote in message
...
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.

Min/Max with IF(?) criteria
 

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


with @tiscali.co.uk


"ronnomad" wrote in message
...
Bob,

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

"Bob Phillips" wrote:

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



"ronnomad" wrote in message
...
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.