Thread: Logical Formula

Is it possible to have the "value if true" or "value if false" be a formula?

If there is data in the "Prepay" column, I'd like the "Total (before tax)" column to calculate with a 3% discount.

Date Received T&S Pest Mgnt T&S Fert Landscape Total (before Tax) Prepay
2.6.12 $620.00 $406.00 $1,026.00 Check

So it would be something like this:
=if(E5>0,[=1.097*sum(B5:C5)],[=sum(B5:C5)]

Thank you!

Oops!It should be multiplied by .97 thanks. I tried the formulas provided below but they didnt work. See my notes in red

Originally Posted by tigeravatar

mweiss1215,

You would just leave out the subsequent = signs, so:
=if(E5>0,1.097*sum(B5:C5),sum(B5:C5))

As a side note, isn't 1.097 increasing it by 9.7% rather than decreasing it by 3%? So this might be more accurate:
=if(E5>0,0.97*sum(B5:C5),sum(B5:C5))

When I tried the above formula, the resule was #VALUE
Also, since you're multiplying the same value ( sum(B5:C5) ) by either 1.097 (or 0.97) or 1 (so it returns itself unmodified), you could this condensed version:
=if(E5>0,1.097,1)*sum(B5:C5)
or, using 0.97:
=if(E5>0,0.97,1)*sum(B5:C5)
When I tried the above formula, the result was #NAME

That worked! Thank you for the quick respones!!

Originally Posted by tigeravatar

mweiss1215,

I am unable to duplicate that issue. Attached is an example workbook based on the criteria you described. In columns F and V are the two formulas I suggested and they both calculate properly.