IF/Then formula

Copper Contributor

I am trying to use an If/Then formula to calculate a value.  I'm using this: =IF(A8< 5000.01,C8=A8*0.05).

The cell that contains the formula is showing "FALSE". I want cell C8 to contain the calculated number.  What am I doing wrong?

 

Thanks.

7 Replies
=IF(A8< 5000.01;A8*0.05;"")

@WMegl In C8 enter:

=IF(A8< 5000.01,A8*0.05,"something else")

 

.... where you can replace "something else" with whatever you want to display if A8 is NOT less then 5000.01.

Neither of the formulas above worked. I'm still getting the word "FALSE: in the cell rather than a value.
I got it to work. I needed the add the THEN formula. Thanks.

@WMegl Why not upload a screenshot of your worksheet showing the formula "at work".

Riny_van_Eekelen: Good idea. Thanks for your help.

I'm using these formulas: =IF(A8< 5000.01,A8*0.05,5000*0.05)
=IF((A8-5000.01)<20000,(A8-5000)*0.04,20000*0.04
=IF((A8-25000)<75000,(A8-25000)*0.03,75000*0.03)
=IF((A8-100000)<300000,(A8-100000)*0.0275,300000*0.0275)
=IF((A8-400000)<600000,(A8-400000)*0.025,600000*0.025)
=IF((A8-1000000)>0,(A8-1000000)*0.02,0)

Amount up to $5K Next $20K Next $75K Next $300K Next $600K Over $1M Total
5% 4% 3% 2 3/4% 2 1/2% 2%
$1,400,000 $250 $800 $2,250 $8,250 $15,000 $8,000 $34,550

$260,000 $250 $800 $2,250 $4,400 $(3,500) $- $4,200

The problem arises when the "Amount" is less than $1M. As shown in the second row, negative amounts begin to appear when total thresholds are met which makes the Total incorrect. I only wish to add the positive number, or show a zero amount when the thresholds are met.

@WMegl 

See the attached sample workbook.