IF statement help please

[SGeorgie]

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

[#2]

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 

[#3]

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!

[#4]

@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?

[#5]

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

[#6]

*12 is for yearly amount

[#7]

Sorry AB is the range
AC is current
AD will be potential

- so how should the formula look please

[#8]

@SGeorgie 

I need this formula in Potential (AD)

[#9]

@SGeorgie 

This is extremely confusing - you have now stated three different sets of columns.

Let's start from scratch. What is the amount you want to add 1.5%, 1% or 0.5% to?

[#10]

Hi

As per subject above
AB is the range
AC is current
AD will be potential

The formula will sit in AD, and it is the relevant % increase to the current comm column (AC)

So if Range says low I want to add 1.5% to the current to equal potential
if mid I want to add 1% to the current to equal potential
if high I want 0.5% added to current to equal potential

Hope thats clearer - added new image
Thank so much

[#11 · Accepted solution]

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

[#12]

@Hans Vogelaar thanks ill give them a go, many thanks

[#13]

@Hans Vogelaar   That's brilliant, thank you.  If there is a very low or very high or no comms range, and I want these to remain the same (ie no increase) how do i write that?

I tried IF(AB2="Very High",+0

but it automaticity removes the '+' sign and puts 0 as the answer

Thanks

[#14]

DONE IT, I put 100% and it worked!

[#15]

@Hans Vogelaar 

So now i need to show if red and blue to look at range 1

if green and yellow to look at range 2

The range is on the Summary tab as shown

The formula as it stands is (which is looking at only 1 range name (low, mid or high etc but NOT based on colour range so all colours are mingled together)

=IF(AND($Z2>=Summary!$C$3,$Z2<=Summary!$D$3),"Low",IF(AND($Z2>=Summary!$C$4,$Z2<=Summary!$D$4),"Mid",IF(AND($Z2>=Summary!$C$5,$Z2<=Summary!$D$5),"High",IF(AND($Z2>Summary!$D$5),"Very High",IF(AND($Z2<Summary!$C$3,$Z2>0),"Very Low",IF(AND($Z2=0),"No Comm"))))))

So what i want if red and blue with Z being >=5 or <=9 "Low", mid >=9.01 <=12 etc

OR if green and yellow and Z being >=4 <=6 "Low", mid >=6.01 <=9.99 etc 

I then need my Current and Potential comm columns to look at the column /ranges to apply the relevant % increase

Current formulas are

Current

=SUM($U2/$V2)*$Z2%*12

Potential

=$AC2*IFS($AB2="Low",(1+Summary!$E$3),$AB2="Mid",(1+Summary!$E$4),$AB2="High",(1+Summary!$E$5),$AB2="Very Low",100%,$AB2="Very High",100%,$AB2="No Comm",100%)

(these currently refer to Range 1 only