CODEWITHSUNDEEP
excelsyntaxexcel-formula
Gino Rondelli
Gino Rondelli

Reputation: 25

Excel IF function always returns first result

I'm trying to write a formula in Excel which take in an employee number field and returns the relevant band of employee. I've just started a new job and the data is somewhat all over the place so trying to automate sorting some of this out. The reason we're looking to sort this is as Employee banding makes reporting and writing SQL queries a million times easier than just the natural occurrence of numbers.

Currently I've gotten as far as:

    =IF(C8>=100,"E: 100-199",IF(C8>=200,"F:200-499",IF(C8>=500,"G: 500-999",IF(C8>=1000,"H: 1000-4999",IF(C8>=5000,"I: 5000 Plus")))))

The above statement makes sense to me but it always returns the first value of "E:100-199" no matter how large the number is e.g. 400 still gives band E. I'm imagining that this means the formula simply doesn't ever get out of the first IF statement and as a result always returns the first answer, but yet i'm not an excel expert and the syntax looks OK to me? Any pointers on my obvious ineptitude are greatly appreciated.

Upvotes: 1

Views: 100

Answers (1)

Ralph
Ralph

Reputation: 9434

200 is also >= 100. Hence, you'll also get for the 200+ the result you merely want for the 100+. So, you have several different choices here how to fix this:

(1) Start with the largest number first:

=IF(C8>=5000,"5000 Plus",IF(C8>=1000,"between 1000 and 4,999",IF(C8>=900,"500-999",IF(

(2) Change the >= to <= and take the upper limits:

=IF(C8<=199,"E: 100-199",IF(C8<=499,"F:200-499",IF(C8<=999,"G: 500-999",IF(...

(3) Make the where clause to include the upper and lower boundary:

=IF(AND(C8>=100,C8<=199),"E: 100-199",IF(AND(C8>=200,C8<=499),"F:200-499",IF(AND(C8>=500,C8<=999),"G: 500-999",IF(...

Upvotes: 2

Related Questions