IF Function with multiple text entries
 

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€�)))))))
--
thanks

IF Function with multiple text entries
 

=IF(U24<0,"text1 text1",IF(AND(U240,U24<0.05),"text2
text2",IF(AND(U240.05,U24<0.1),"text3 text3",IF(AND(U240.1,U24<0.25),"text4
text4",IF(AND(U240.25,U24<0.5),"text5 text5",IF(AND(U240.5,U24<0.75),"text6
text6",IF(AND(U240.75,U24<1),"text 7 text7","text 8 text8")))))))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Morgan" wrote:

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€�)))))))
--
thanks

IF Function with multiple text entries
 

On Thu, 10 Dec 2009 21:28:01 -0800, Morgan
wrote:

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

IF Function with multiple text entries
 

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.

--
Wag more, bark less


"Ron Rosenfeld" wrote:

On Thu, 10 Dec 2009 21:28:01 -0800, Morgan
wrote:

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
.

IF Function with multiple text entries
 

On Fri, 11 Dec 2009 06:20:01 -0800, Brad
wrote:

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

IF Function with multiple text entries
 

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


"Ron Rosenfeld" wrote:

On Fri, 11 Dec 2009 06:20:01 -0800, Brad
wrote:

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
.

IF Function with multiple text entries
 

thanks everyone, that's a great help, cheers


"Brad" wrote:

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


"Ron Rosenfeld" wrote:

On Fri, 11 Dec 2009 06:20:01 -0800, Brad
wrote:

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
.