Forum Discussion
IF statement help please
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%)
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
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%)
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
HansVogelaar 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
- DONE IT, I put 100% and it worked!
HansVogelaar thanks ill give them a go, many thanks