Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function - Calculation not working
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function - Calculation not working
One way:
=MAX(C7-2000,0)*10%+MAX(C7-6000,0)*5%+MAX(C7-10000,0)*5% Tim C "amandooshna" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function - Calculation not working
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function - Calculation not working
I noticed in the formula that your conditions are as ff:
a. if C7 =10000 b. if C7 <=10000 c. if C7 <=6000 d. if C7 <=2000 Try removing the "=" sign on either condition a or b, since if the value of C7 = 10000, conditions a & b are TRUE, and this could be the error since Excel will follow the first condition. Same also applies when the value of C7 = 2000 orless, conditions b, c, & d are all TRUE, hence Excel will follow condition b. Also, try to use the AND function to specifiy the range of value you use on your conditions, ie: =IF(AND(C7<=10000,C7=6000),CONDITION1,CONDITION2) Hope this helped. -- sandyboy "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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Function - Calculation not working | Excel Worksheet Functions | |||
Workday calculation - not working | Excel Worksheet Functions | |||
Calculation not working | New Users to Excel | |||
Auto Calculation not working | Excel Worksheet Functions | |||
Automatic calculation is not working | Excel Discussion (Misc queries) |