Forum Discussion

SGeorgie's avatar
SGeorgie
Brass Contributor
Nov 03, 2022

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

 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Nov 03, 2022

    SGeorgie 

    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%)

  • Jihad Al-Jarady's avatar
    Jihad Al-Jarady
    Steel Contributor
    Nov 03, 2022

    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 

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Nov 03, 2022
      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!
    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Nov 03, 2022
      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
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 03, 2022

    SGeorgie 

    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?

    • SGeorgie's avatar
      SGeorgie
      Brass Contributor
      Nov 03, 2022
      Sorry AB is the range
      AC is current
      AD will be potential

      - so how should the formula look please