Problem with a conditional Min formula
 

I have the following in my spreadsheet (I hope you can see the layout?):

A B C D
1 Counterparty Price Nov-08 Dec-08
2 Customer A $7.830 675
3 Customer A $7.800 500 500
4 Customer A $8.510 1000
5 Total Volume 1500 1175
6 W. Avg. Cost $8.273 $7.817
7
8 Customer B $6.050 5
9 Customer B $5.650 200
10 Customer B $7.750 30
11 Total Volume 235 0
12 W. Avg. Cost $5.927 $0.000
13
14 Total Volume 1735 1175
15 Avg $ $7.955 $7.817
16 High $8.510 $7.830
17 Low $5.650 $0.000

Formula in Cell D17
=IF(D14=0,"",MIN(MIN(IF(D$2:D$4<"",$B$2:$B$4)),MI N(IF(D$8:D$10<"",$B$8:$B$10))))

this formula gives me a value of zero, but it should be $7.80. I don't know
why, but whenever the cells are blank (or "" as is generated by another
formula) in any given customer section, then the MIN formula gives me a zero.


Any suggestions or help would be appreciated!
Thanks.

Problem with a conditional Min formula
 

That's a lot of MIN's!

My understanding is, if either D14 or all 3 value from D2 - D4 or all values
from D8 - D10 are blank, then blank?

Otherwise, take minimum of sum from B2 to B4 and sum from B8 to B10...

=if(OR(D14=0,sum(D$2:D$4)=0,sum(D$8:D$10)=0),"",mi n(sum($B$2:$B$4),sum($B$8:$B$10))

Meant to do something else? let us know.

:-)

"SCarroll" wrote:

I have the following in my spreadsheet (I hope you can see the layout?):

A B C D
1 Counterparty Price Nov-08 Dec-08
2 Customer A $7.830 675
3 Customer A $7.800 500 500
4 Customer A $8.510 1000
5 Total Volume 1500 1175
6 W. Avg. Cost $8.273 $7.817
7
8 Customer B $6.050 5
9 Customer B $5.650 200
10 Customer B $7.750 30
11 Total Volume 235 0
12 W. Avg. Cost $5.927 $0.000
13
14 Total Volume 1735 1175
15 Avg $ $7.955 $7.817
16 High $8.510 $7.830
17 Low $5.650 $0.000

Formula in Cell D17
=IF(D14=0,"",MIN(MIN(IF(D$2:D$4<"",$B$2:$B$4)),MI N(IF(D$8:D$10<"",$B$8:$B$10))))

this formula gives me a value of zero, but it should be $7.80. I don't know
why, but whenever the cells are blank (or "" as is generated by another
formula) in any given customer section, then the MIN formula gives me a zero.


Any suggestions or help would be appreciated!
Thanks.