IF AND formula help

[southernrdn]

I need 

if (G8>7,G8<23,1) (g8>23,g8<38,2) (g>38,g8<53,3)...

plus more data, same qualifications, basically, if a number is in a certain range, I want the next cell to say "1", then if it is another range, "2", etc. ranges being:

>7<23, THEN 1

>23<38, THEN 2

ETC, for up to the number "8".

Please advise.

[#2]

@southernrdn 

It's a little difficult to follow, but certainly sounds as if you'd benefit from the IFS function rather than the IF function. IFS takes multiple conditions, one at a time, and returns the first result from the first condition that is TRUE. Here's a reference that might help you structure it.

https://exceljet.net/excel-functions/excel-ifs-function

[#3]

@mathetes Yes, I think that is it, but I need a range for the values. So, I want IF the number is greater than or equal to 8, up to 22, then "1", IF the number is greater than or equal to 23, up to 37, then "2".

And on...

This is what I have but it is not working, Advise? Right now I have:

=IFS (G8>7,G8<22,"1" g8>22,g8<37,"2"g>37,g8<52,"3")

[#4 · Accepted solution]

@southernrdn 

You need to structure it so there's a single condition followed by single consequences. So in your case, you need to combine the min and max of the range with AND. Try this, or some variation on it

=IFS(AND(G8>7,G8<22),1,AND(G8>22,G8<37),2..........)

By the way, are the values in G8 always integers? If they're fractions, then you will want to make the boundary values finely tuned, fine enough to catch such things as 22.5. Right now, that would not meet either range definition.

If there's a clearly defined mathematical relationship between each successive range's maximum value, if, for example, they're all multiples of 8) you could also use

=CHOOSE(G8/8,1,2,3,4

[#5]

That worked! thanks so much!