Inserting text in a cell

[Annmad]

Hi,
I want a text to appear in a cell based on the value in the cell before . This is my formula:

=IF(($AR6=1),"General Communication Periodic Monitoring",IF(AND($AR6>1,$AR6<=2),"General Communication Periodic Monitoring Safety Procedure Training",IF(AND($AR6>2,$AR6<=3),"Specific Hazard Communication Work Practice Control Periodic Monitoring",IF(AND($AR6>3,$AR6<=4)," Medical Surveillance Work Practice Control Exposure Control Plan Periodic Monitoring",IF(AND($AR6>4,$AR6<=5),"Engineering Control Respirator Medical Surveillance Work Practice Control Exposure Control Plan Periodic Monitoring ")))))

I don't know what is the mistake. The text doesn't appear. Can you help me, please?

 

[Micron]

Seems to work for me.

Perhaps you have an incorrect cell reference, or your number values are in the wrong cells?

 

[Cubist]

Consider using CHOOSE() to eliminate the nested IF.

=CHOOSE($AR16,"General Communication Periodic Monitoring",
"General Communication Periodic Monitoring Safety Procedure Training",
"Specific Hazard Communication Work Practice Control Periodic Monitoring",
"Medical Surveillance Work Practice Control Exposure Control Plan Periodic Monitoring",
"Engineering Control Respirator Medical Surveillance Work Practice Control Exposure Control Plan Periodic Monitoring")

 

[Annmad]

Seems to work for me.
View attachment 109458
Perhaps you have an incorrect cell reference, or your number values are in the wrong cells?

Ok, I will do.
Instead of getting the text, I have got False.
Thank you,

 

[Joe4]

Ok, I will do.
Instead of getting the text, I have got False.
Thank you,

Note that if your numbers are entered as text instead of valid numeric entries, you would need to use text qualifiers around the numbers in your formula, i.e.
instead of:

=IF(($AR6=1...

it would need to look like:

=IF(($AR6="1"...

and likewise for all the other options.

Or simply fix all your entries to be valid numbers (format cell as number and re-enter values).

 

[Micron]

Still works for me if I format the cells as text. Doesn't work if that is done via preceding apostrophe - '1
I'm not a fan of the nested if's either.

 

[Joe4]

Still works for me if I format the cells as text. Doesn't work if that is done via preceding apostrophe - '1

In Excel, changing the format of a cell after the entry already exists does not change the data type, so that is expected behavior.
Try changing the format of the cell to text BEFORE data entry, and see what happens! (It is basically the equivalent of typing an apostrophe before the number).

Sometimes when data comes from external sources (the Web, other programs, etc), numeric entries will come in as text.
A quick & easy way to fix them a column at a time is to select the the column, go to "Text to Columns" from the Data menu, and click "Finish".

 

[Micron]

Well, that clears up some odd behaviours that I've experienced in the past. Is it OK to say that is one of the most stupid things I've seen in a long time? You get around this how, by deleting the "poisoned" cells? If so, that must be fun if there's data all around them that you need to keep in place.

OK, if I change the format back to number and re-enter the same values, it's fixed. Still, really, really dumb.

 

[Annmad]

Note that if your numbers are entered as text instead of valid numeric entries, you would need to use text qualifiers around the numbers in your formula, i.e.
instead of:

=IF(($AR6=1...

it would need to look like:

=IF(($AR6="1"...

and likewise for all the other options.

Or simply fix all your entries to be valid numbers (format cell as number and re-enter values).

Hi Joe4,that
I formatted column AR6 as a number and redone the formula, but I still have the answer False instead of the text corresponding to the value in cell AR6.
Thank you,

 

[Micron]

Hi Joe4,that
I formatted column AR6 as a number and redone the formula, but I still have the answer False instead of the text corresponding to the value in cell AR6.
Thank you,

See my edited post.