• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

if(and statement - URGENT HELP NEEDED

S

samantha

New Member
can you tell me what I am doing wrong this formula is not working

=IF(AND(AG5>=1,AG5<=174550),AG5*18%,IF(AND(AG5>=174551,AG5<=272700),AG5-174550*25%+31419, IF(AND(AG5>=272701, AG5<=377450), AG5-272700*30%+55957,IF(AND(AG5>=377451, AG5<=528000),AG5-377450*35%+87382, IF(AND(AG5>=528001,AG5<=673100),AG5-528000*38%+140074,IF(AND(AG5>=673101,AG5<1000000),AG5-673100*40%+195212,""))))))
 
Other than being very unwieldy, there doesn't seem to be anything wrong with the formula itself. Perhaps a sample file would help to diagnose the issue?
 
lol on unwieldy I am just a baby exceler :) I have uploaded a file please help
 

Attachments

  • problem code.xlsx
    8.5 KB · Views: 4
Without worrying about making the formula itself better - some others on here would be much better at that than I, try this:

=IF(AND(AG5>=1,AG5<=174550),AG5*18%,IF(AND(AG5>=174551,AG5<=272700),((AG5-174550)*25%)+31419, IF(AND(AG5>=272701, AG5<=377450), ((AG5-272700)*30%)+55957,IF(AND(AG5>=377451, AG5<=528000),((AG5-377450)*35%)+87382, IF(AND(AG5>=528001,AG5<=673100),((AG5-528000)*38%)+140074,IF(AND(AG5>=673101,AG5<1000000),((AG5-673100)*40%)+195212,""))))))
 
Your problem is the AG5-174550*25%+31419 part isn't doing what you want it to do, because maths doesn't work in just a line - the added brackets make sure it is working each step out at a time and then doing the next part.
 
samantha said:
its giving me some weird no of R547 286.10 - it supposed to give me an answer of R13 140.98
Click to expand...

the amount it must find is the section (IF(AND(A2>=528001,A2<=673100),A2-528000*38%+140074,) on the formula to give the answer R13 140.98 but its not
 
caladanbrood said:
Your problem is the AG5-174550*25%+31419 part isn't doing what you want it to do, because maths doesn't work in just a line - the added brackets make sure it is working each step out at a time and then doing the next part.
Click to expand...
thanks I tried it still not working out uuurgggggggg!
 
well from what i worked out your looking to get 267138.1 .... if you look at your code you will see you need brackets as shown
as you have no brackets to break down the - and the multiplication and adding

you will also need to go through the rest as you will see similar for all your ... value if through sections of your code.

=IF(AND(A2>=1,A2<=174550),A2*18%,IF(AND(A2>=174551,A2<=272700),A2-174550*25%+31419, IF(AND(A2>=272701, A2<=377450), A2-272700*30%+55957,IF(AND(A2>=377451, A2<=528000),A2-377450*35%+87382, IF(AND(A2>=528001,A2<=673100),A2-(528000*38%+140074),IF(AND(A2>=673101,A2<1000000),A2-673100*40%+195212,""))))))
 
This is also a prime example of where to use a lookup table. Rather than having all those values coded into the formula, which makes it hard to read and hard to change, if you built a table like this:
upload_2014-11-25_11-27-52.png
Your formula in B2:
=(A2-(LOOKUP(A2,K2:K9)-1))*LOOKUP(A2,K2:L9)+LOOKUP(A2,K2:M9)

Now we can see that we are taking the value in A2 and subtracting something. We then multiply that by a percentage, and add one final number. :awesome:
 
Hi Samantha,

I would suggest to create a lookup table (as shown in attached sample file) to do the calculations.

Benefits:

The formula will become smaller. Easier to read.
If anytime percentage changes or value need to be added or new limits comes, it just need to be updated in table and all formula will update automatically compared to your hard coding the values in formulas.

Regards,
 

Attachments

  • problem code.xlsx
    10.2 KB · Views: 5
You must log in or register to reply here.
Back
Top