IF Function with multiple text entries

[Morgan]

hi,

i've had a go at creating an IF funtion that will display one of a number of
phrases based on a particular value, however it comes up with some kind of
name error, what i'm after is i want it to display one piece of text if the
value is less than zero, another piece of text if the value is between zero
and 0.05 and so on, with the last IF function being if the value is less than
1 and greater than 0.75 display text7 and greater than 1 display text 8, does
somebody know how to fix it?

below is what i've got, i've swapped the actual text out for ease of use


=IF(U24<0,â€text1 text1â€,IF(U24<0.05,â€text2 text2â€,IF(U24<0.1,â€text3 text
3â€,IF(U24<0.25,â€text4 text4â€,IF(U24<0.5,â€text5 text5â€,IF(U24<0.75,"text6
text6",IF(U24<1,â€text 7 text7â€,â€text 8 text8â€)))))))

 

[Ms-Exl-Learner]

=IF(U24<0,"text1 text1",IF(AND(U24>0,U24<0.05),"text2
text2",IF(AND(U24>0.05,U24<0.1),"text3 text3",IF(AND(U24>0.1,U24<0.25),"text4
text4",IF(AND(U24>0.25,U24<0.5),"text5 text5",IF(AND(U24>0.5,U24<0.75),"text6
text6",IF(AND(U24>0.75,U24<1),"text 7 text7","text 8 text8")))))))

Remember to Click Yes, if this post helps!

 

[Ron Rosenfeld]

hi,

i've had a go at creating an IF funtion that will display one of a number of
phrases based on a particular value, however it comes up with some kind of
name error, what i'm after is i want it to display one piece of text if the
value is less than zero, another piece of text if the value is between zero
and 0.05 and so on, with the last IF function being if the value is less than
1 and greater than 0.75 display text7 and greater than 1 display text 8, does
somebody know how to fix it?

below is what i've got, i've swapped the actual text out for ease of use


=IF(U24<0,”text1 text1”,IF(U24<0.05,”text2 text2”,IF(U24<0.1,”text3 text
3”,IF(U24<0.25,”text4 text4”,IF(U24<0.5,”text5 text5”,IF(U24<0.75,"text6
text6",IF(U24<1,”text 7 text7”,”text 8 text8”)))))))

Much simpler to use a lookup table.

Note that your formula also means that if U24=0, you want "text2".

Keeping that in mind, set up a table someplace in your workbook. I used M1:N7
on the same worksheet. Note that it starts with 0 and text2.

0 text2
0.05 text3
0.1 text4
0.25 text5
0.5 text6
0.75 text7
1 text8

Then use this formula:

=IF(U24<0,"text1",VLOOKUP(U24,M1:N8,2))


--ron

 

[Brad]

Taking Ron's idea one step further
add one more row to the table (on top)

-10^99 text1

then the formula would be
VLOOKUP(U24,M1:N9,2) - note that the range has increased by one.

 

[Ron Rosenfeld]

Taking Ron's idea one step further
add one more row to the table (on top)

-10^99 text1

then the formula would be
VLOOKUP(U24,M1:N9,2) - note that the range has increased by one.

For some reason, I was trying to set that up before and couldn't. Must have
been too early in the morning, but it is preferable. The range should be M1:N8
though.
--ron

 

[Brad]

You are correct - In my post, I simply added one row to your original range

 

[Morgan]

thanks everyone, that's a great help, cheers

You are correct - In my post, I simply added one row to your original range