Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting - problem with text cell value from formula | Excel Worksheet Functions | |||
Conditional Formula Problem... | Excel Worksheet Functions | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
Conditional formatting with dates formula problem. | Excel Discussion (Misc queries) |