MIN greater than zero with a true false option

[MAHONEY21]

Help Me PLease!

I am working on an estimate sheet at work and cannot seem to create a formula that will allow all options to work.

I need to create a formula that will reference the minimum of one column but it must only choose numbers greater than zero. I also have a check box inserted in the table to identify whether the costs entered are lump sum or per unit pricing. If the statement is false or unchecked it will multiply the MIN of these cells times a quantity cell.

I have been able to have it ignore 0 for when the statement is tru (check box marked) but when the statement is false it reverts to 0 as the minimum number.

This is the formula i have so far....


=IF(G9=TRUE,MIN(IF(F9:F14>0,F9:F14)),MIN(IF(F9:F14>0,F9:F14)*G12))

using Shift Ctrl & Enter to enter the formula...

G9 is the check box or the true false statement.

F9 : F14 is a list of quote or values

G12 is the quantity that is being purchased

If any one could help me i would greatly appreciate it, this is driving me nuts!!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

 

[schielrn]

Welcome to the Board!

Is G12 blank or 0, that is the only way I can see the false part returning 0 if the true part works as you say, since it is essentially the same formula?

 

[MAHONEY21]

It is not a 0 or blank.

 

[schielrn]

Actually you ahev the *G12 in the wrong palce, try this:


=IF(G9=TRUE,MIN(IF(F9:F14>0,F9:F14)),MIN(IF(F9:F14>0,F9:F14))*G12)

Hope that helps.

 

[MAHONEY21]

Thank you!!!!!!!!!!!!!!!!!!!!!!!