Multiple IF/AND Statements Formula

Brennan Montoni

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!

Kelly Moore

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

Amanda Alv

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

Kelly Moore

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

Brennan Montoni

Thanks Kelly!