Forum Discussion
IF statement help please
Hi
How do I write an IF statement if I want it to look for different ranges and if that meets certain criteria to do a SUM calculation.
For example
So if range is 'Low' I want it to add 1.5% onto current column
if range is 'Mid' I want to add 1% onto current column
and if range is 'High' I want to add 0.5% onto current column
I have tried this formula but it errors saying the = looks like a formula and to add an apostrophe before = , so '= but this does not work either
=IF(AND(AB2 ="Low",(SUM(U2/V2)*SUM(Z2+1.5)%*12),IF(AND(AB2 ="Mid", (SUM(U2/V2)*SUM(Z2+1)%*12),IF(AND(AB2 ="High", (SUM(U2/V2)*SUM(Z2+0.5)%*12)))))))
I'm thinking i have not ut the brackets in correct place maybe......??? Help please
Thanks
In AD2:
=AC2*IF(AB2="Low",101.5%,IF(AB2="Mid",101%,IF(AB2="High",100.5%)))
or
=AC2*IFS(AB2="Low",101.5%,AB2="Mid",101%,AB2="High",100.5%)
Hi SGeorgie
You have several issue, one of them the brackets, but this is not the main problem.
what is column Z that mention in the formula, is it the same column you want to put the formula? You can not write the formula and use the same cell inside the formula, that will make loop.
You should use the percentage in different way, such as %12 is 1.2
- hi, this formula will sit in the potential comm column (column AD)
Column Z is the agreecomm (as pictured)
So I want column AD to check column AB (range) and if low/mid/high to add 1.5/1/0.5 % accordingly
Could you help show me what formula should be? Been working on this all morning and stumped! - The formula I have used in Current (column AC) is =SUM(U2/V2)*Z2%*12 which works fine, so i thought I could just say if range is XXX then add X%, but if easier to write formula anothr formula I could use in column AD with an IF STATEMENT TO LOOK AT RANGE is:
=SUM(U2/V2)*SUM(Z2+X)%*12
There is no need to use SUM in this formula.
In your screenshot, "Low" etc. is in column AA and Current is in column AB, but in your formula "Low" is in AB, while Current is in Z?
What is the AND for?
Why do you multiply with 12?
