Multiple IF/AND Statements Formula
Hi All,
I have a field "Annual Hours Saved". I want to create another column with the following logic:
If Annual Hours Saved is 0 to 10, then put 1
If Annual Hours Saved is 11 to 20, then put 2
If Annual Hours Saved is 21 to 50, then put 3
If Annual Hours Saved is more than 51, then put 4
Any help would be appreciated! Thanks in advance!
Best Answer

Hey @Brennan Montoni
Try this
=IF([Annual Hours Saved]@row<0, 1, IF([Annual Hours Saved]@row<=10,1,IF([Annual Hours Saved]@row<=20,2, IF([Annual Hours Saved]@row<=50, 3, IF([Annual Hours Saved]@row>50,4)))))
With no screenshot, I wasn't sure if your [Annual Hours Saved] could ever be negative. I accounted for that as a 1. Change if incorrect.
Kelly
Answers

Hi there,
Using multiple AND statements within an IF statement will get you there; the formula below will give you those answers and let you convert it to a column formula if that is more helpful.
=IF(AND([Annual Hours Saved]@row >= 0, [Annual Hours Saved]@row <= 10), "1", IF(AND([Annual Hours Saved]@row >= 11, [Annual Hours Saved]@row <= 20), "2", IF(AND([Annual Hours Saved]@row >= 21, [Annual Hours Saved]@row <= 50), "3", IF([Annual Hours Saved]@row >= 51, "4"))))

Hey @Brennan Montoni
Try this
=IF([Annual Hours Saved]@row<0, 1, IF([Annual Hours Saved]@row<=10,1,IF([Annual Hours Saved]@row<=20,2, IF([Annual Hours Saved]@row<=50, 3, IF([Annual Hours Saved]@row>50,4)))))
With no screenshot, I wasn't sure if your [Annual Hours Saved] could ever be negative. I accounted for that as a 1. Change if incorrect.
Kelly

Thanks Kelly!
Help Article Resources
Categories
Check out the Formula Handbook template!