IF not working

[amandooshna]

=IF(C7>=10000,SUM(6000*10%)+(4000*15%)+((C7-10000)*20%),IF(C7<=10000,IF(B7>6000,SUM(6000*10%)+((C7-6000)*15%),IF(C7<=6000,SUM((C7-2000)*10%),IF(C7<=2000,0)))))

This above sum works, execpt for the last IF function. For some reason my
result always comes out with a negative, when it should be 0. Any
suggestions on why? Or what I should do to change?

Thanks!

 

[Teethless mama]

=IF(C7>=10000,SUM(6000*10%)+(4000*15%)+((C7-10000)*20%),IF(C7<=10000,IF(B7>6000,SUM(6000*10%)+((C7-6000)*15%),IF(C7<=2000,0,IF(C7<=6000,SUM((C7-2000)*10%,0))))))

 

[Greg Wilson]

See my response to your original post.

Greg

 

[amandooshna]

Thanks all for your help. Greg, I tried you final IF sum that you suggested,
however my final figure (if less then 2000) still comes out as a negative
number, and I need it to end as 0.

=IF(C7>=10000,SUM(6000*10%)+(4000*15%)+((C7-10000)*20%),IF(C7<=10000,IF(C7>6000,SUM(6000*10%)+((C7-6000)*15%),IF(C7<=6000,SUM((C7-2000)*20%),IF(C7<=2000,0)))))

I know the first two IF's are working, however my last IF isn't. So when I
change my figures, it should total to 0 at the end if less then 2000...it is
driving me nuts because I can't seem to get it to work....

 

[Greg Wilson]

My formula below, which is the one I suggested, returns 0 when C7 <2000 ?

=If(C7>=10000, 1200 + (C7-10000) * 0.2, If(C7>6000, 600 + (C7-6000) * 0.15,
If(C7> 2000, (C7-2000) * 0.1, 0)))

As I mentioned in my first post, the reason the C7<=2000 part doesn't
execute is because this condition is included in the condition C7<=6000. If,
for example, C7 equals 900 then this is <=6000 so the SUM((C7-2000)*20%) will
be executed returning a negative value. The C7<=6000 condition is parent to
the C7<=2000 condition and will be evaluated first. IF AND ONLY IF it is
False will the <=2000 be evaluated.

There are redundancies in your code. That is why I was able to simplify it
quite a bit. Recall that I did assume that the B7 in your formula was
supposed to be C7. If this is a false assumption then my formula is wrong.

Greg

 

[amandooshna]

That one comes out with a negative still, not zero. I am dumbfounded on this
one.

 

[Roger Govier]

Hi

Try
=C7*10%+MAX(0,(C7-6000))*5%+MAX(0,C7-10000)*5%

 

[Roger Govier]

Hi

Sorry, missed the bit about the first 2000 not attracting any
percentage, it should be

=MAX(0,(C7-2000)*10%+MAX(0,(C7-6000))*5%+MAX(0,C7-10000)*5%)

 

[sandyboy]

I noticed that the formula contains the ff conditions:

a. If C7>=10000
b. If C7<=10000
c. If C7<=6000
d. If C7<=2000

Try to remove the equal sign on either condition a or b, since if the value
of C7 is 10000, both conditions a and b are TRUE, therefore Excel will
execute the first condition.

This is also true when the value of C7 <=2000, conditions b, c, and d, are
all true, but the formula will first read condition b and therefore will
execute the corresponding function.

Try to use the function AND to specify on your condition the range of a cell
value for it to execute the function. Ie:

=IF(AND(C7<=10000,C7>6000),"TRUE","FALSE")

Hope this helped.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(C7>{0,6000,10000}),(C7-{0,6000,10000}),
{0.1,0.05,0.05})*(C7>=2000)

See

http://www.mcgimpsey.com/excel/variablerate.html

for an explanation of how it works, and some ways to make it more
flexible for changes to the formula.

The reason your formula doesn't work (aside from having B7 instead of C7
in one location), is that if C7<=6000, the SUM will always be executed
so the last IF() is never evaluated.

Note that your SUM()s are superfluous. SUM(6000*10%) just adds the
overhead of the SUM function to its equivalent (6000*10%).