Conditional formula using MIN function doesn't work
 

I am trying to find the MIN price for rows that have a volume attached. So
in the example below I want the result to equal $8.25. The formula I'm using
is =MIN(IF($b$2:$b$40,$a$2:$a$4,0)). I've also tried
=MIN(IF($b$2:$b$4<"",$a$2:$a$4,0)). As soon as there is a blank cell in
column B I get the formula to return zero.

$ volume
$8.25 300
$8.67 200
$8.17

This modification of the conditional SUM formula works for MAX and AVERAGE,
but not MIN. Any ideas? I'm using Excel 2003.

Conditional formula using MIN function doesn't work
 

Remove the 0:

=MIN(IF($B$2:$B$40,$A$2:$A$4))

Don't forget to array enter!

--
Biff
Microsoft Excel MVP


"SCarroll" wrote in message
...
I am trying to find the MIN price for rows that have a volume attached. So
in the example below I want the result to equal $8.25. The formula I'm
using
is =MIN(IF($b$2:$b$40,$a$2:$a$4,0)). I've also tried
=MIN(IF($b$2:$b$4<"",$a$2:$a$4,0)). As soon as there is a blank cell in
column B I get the formula to return zero.

$ volume
$8.25 300
$8.67 200
$8.17

This modification of the conditional SUM formula works for MAX and
AVERAGE,
but not MIN. Any ideas? I'm using Excel 2003.

Conditional formula using MIN function doesn't work
 

There is no 0 in the cell, it is blank. I also the array enter. Other
ideas?!? I'm completely out of ideas! :)

"T. Valko" wrote:

Remove the 0:

=MIN(IF($B$2:$B$40,$A$2:$A$4))

Don't forget to array enter!

--
Biff
Microsoft Excel MVP


"SCarroll" wrote in message
...
I am trying to find the MIN price for rows that have a volume attached. So
in the example below I want the result to equal $8.25. The formula I'm
using
is =MIN(IF($b$2:$b$40,$a$2:$a$4,0)). I've also tried
=MIN(IF($b$2:$b$4<"",$a$2:$a$4,0)). As soon as there is a blank cell in
column B I get the formula to return zero.

$ volume
$8.25 300
$8.67 200
$8.17

This modification of the conditional SUM formula works for MAX and
AVERAGE,
but not MIN. Any ideas? I'm using Excel 2003.

Conditional formula using MIN function doesn't work
 

You need to array enter it, Biff's formula works for me

blank = 0 in Excel

--


Regards,


Peo Sjoblom

"SCarroll" wrote in message
...
There is no 0 in the cell, it is blank. I also the array enter. Other
ideas?!? I'm completely out of ideas! :)

"T. Valko" wrote:

Remove the 0:

=MIN(IF($B$2:$B$40,$A$2:$A$4))

Don't forget to array enter!

--
Biff
Microsoft Excel MVP


"SCarroll" wrote in message
...
I am trying to find the MIN price for rows that have a volume attached.
So
in the example below I want the result to equal $8.25. The formula I'm
using
is =MIN(IF($b$2:$b$40,$a$2:$a$4,0)). I've also tried
=MIN(IF($b$2:$b$4<"",$a$2:$a$4,0)). As soon as there is a blank cell
in
column B I get the formula to return zero.

$ volume
$8.25 300
$8.67 200
$8.17

This modification of the conditional SUM formula works for MAX and
AVERAGE,
but not MIN. Any ideas? I'm using Excel 2003.

Conditional formula using MIN function doesn't work
 

Thank you both! I got it to work... I had to make a small change in my
actual spreadsheet with the formula so that it read
MIN(IF($B$2:$B$4<"",$A$2:$A$4)) and it works perfectly now! Sorry I didn't
get it the first time... the volume column was also formula generated so
blanks were actually "". THANK YOU!!! That was totally stressing me out and
now looks so simple. :)

"Peo Sjoblom" wrote:

You need to array enter it, Biff's formula works for me

blank = 0 in Excel

--


Regards,


Peo Sjoblom

"SCarroll" wrote in message
...
There is no 0 in the cell, it is blank. I also the array enter. Other
ideas?!? I'm completely out of ideas! :)

"T. Valko" wrote:

Remove the 0:

=MIN(IF($B$2:$B$40,$A$2:$A$4))

Don't forget to array enter!

--
Biff
Microsoft Excel MVP


"SCarroll" wrote in message
...
I am trying to find the MIN price for rows that have a volume attached.
So
in the example below I want the result to equal $8.25. The formula I'm
using
is =MIN(IF($b$2:$b$40,$a$2:$a$4,0)). I've also tried
=MIN(IF($b$2:$b$4<"",$a$2:$a$4,0)). As soon as there is a blank cell
in
column B I get the formula to return zero.

$ volume
$8.25 300
$8.67 200
$8.17

This modification of the conditional SUM formula works for MAX and
AVERAGE,
but not MIN. Any ideas? I'm using Excel 2003.

Conditional formula using MIN function doesn't work
 

That was totally stressing me out

There seems to a lot of that going around these last few days! <g

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"SCarroll" wrote in message
...
Thank you both! I got it to work... I had to make a small change in my
actual spreadsheet with the formula so that it read
MIN(IF($B$2:$B$4<"",$A$2:$A$4)) and it works perfectly now! Sorry I
didn't
get it the first time... the volume column was also formula generated so
blanks were actually "". THANK YOU!!! That was totally stressing me out
and
now looks so simple. :)

"Peo Sjoblom" wrote:

You need to array enter it, Biff's formula works for me

blank = 0 in Excel

--


Regards,


Peo Sjoblom

"SCarroll" wrote in message
...
There is no 0 in the cell, it is blank. I also the array enter. Other
ideas?!? I'm completely out of ideas! :)

"T. Valko" wrote:

Remove the 0:

=MIN(IF($B$2:$B$40,$A$2:$A$4))

Don't forget to array enter!

--
Biff
Microsoft Excel MVP


"SCarroll" wrote in message
...
I am trying to find the MIN price for rows that have a volume
attached.
So
in the example below I want the result to equal $8.25. The formula
I'm
using
is =MIN(IF($b$2:$b$40,$a$2:$a$4,0)). I've also tried
=MIN(IF($b$2:$b$4<"",$a$2:$a$4,0)). As soon as there is a blank
cell
in
column B I get the formula to return zero.

$ volume
$8.25 300
$8.67 200
$8.17

This modification of the conditional SUM formula works for MAX and
AVERAGE,
but not MIN. Any ideas? I'm using Excel 2003.