IF Function - Calculation not working
There are redundancies in your formula which I have removed plus I have
converted SUM(6000*10%)+(4000*15%) to its result of 1200 and SUM(6000*10%) to
600 because these are constants. BTW, the Sum function used here serves no
purpose. I have also assumed that the reference to B7 was intended to be C7.
Your problem resides in this snippet:
IF(C7<=6000,SUM((C7-2000)*10%),IF(C7<=2000,0))
Why would the condition C7<=2000 execute if it is child to the condition
C7<=6000 which encompasses the condition that C7<=2000 ?
Following is my suggested revision:
If(C7=10000, 1200 + (C7-10000) * 0.2, If(C76000, 600 + (C7-6000) * 0.15,
If(C7 2000, (C7-2000) * 0.1, 0)))
Regards,
Greg
"amandooshna" wrote:
Thank you - I have looked further into my problem, and my sum is correct for
what I am trying to achieve, however I have found the last IF sum won't work:
=IF(C7=10000,SUM(6000*10%)+(4000*15%)+((C7-10000)*20%),IF(C7<=10000,IF(B76000,SUM(6000*10%)+ ((C7-6000)*15%),IF(C7<=6000,SUM((C7-2000)*10%),IF(C7<=2000,0)))))
IF the C7 column is less then 2000 then it should equal 0 - however this
doen't work - my figure keeps coming up as a negative?! What have I done
wrong?
"Teethless mama" wrote:
=IF(C7<=2000,0,IF(C7<=6000,(C7-2000)*10%,IF(AND(C7<=10000,B76000),6000*10%+(C7-6000)*15%,6000*10%+4000*15%+(C7-10000)*20%)))
"amandooshna" wrote:
I have sum:
=IF(C7=10000,SUM(6000*10%)+(4000*15%)+((C7-10000)*20%),IF(C7<=10000,IF(B76000,SUM(6000*10%)+ ((C7-6000)*15%),IF(C7<=6000,SUM((C7-2000)*10%),IF(C7<=2000,0)))))
And this works, however when I change one of the input figures, then the
total comes our wrong.
my calculation is based on 10% from $2m - $6m, 15% from $6m-$10m and 20%
$10m and above.
Have I got my sum wrong?
|