| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't get array formula to work in MATCH function | Excel Worksheet Functions | |||
| SUMIF function formula won't work, help | Excel Discussion (Misc queries) | |||
| If function question in conditional formula | Excel Discussion (Misc queries) | |||
| column to column conditional formatting won't work, need formula | Excel Worksheet Functions | |||
| why doesn't my conditional formula work? | Excel Discussion (Misc queries) | |||
Hybrid Mode
