Please fix this formula

[srizki]

<TABLE style="WIDTH: 337pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=449 border=0 x:str><COLGROUP><COL style="WIDTH: 337pt; mso-width-source: userset; mso-width-alt: 16420" width=449><TBODY><TR style="HEIGHT: 18pt" height=24><TD class=xl24 dir=ltr style="BORDER-RIGHT: lightgrey 0.5pt solid; BORDER-TOP: lightgrey 0.5pt solid; BORDER-LEFT: lightgrey 0.5pt solid; WIDTH: 337pt; BORDER-BOTTOM: lightgrey 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: white" width=449 height=24 x:str="'=IF(U2,{"Jury Duty","Waiting","Personal Absence","military","Absence"},{"other"},U2)">=IF(U2,{"Jury Duty","Waiting","Personal Absence","military","Absence"},{"other"},U2)</TD></TR></TBODY></TABLE>

Thanks

 

[Ruddles]

What do you want it to do? In what way does it need fixing?

 

[srizki]

What do you want it to do? In what way does it need fixing?

I want to replace all that to "other".

 

[kidwispa]

Try...

=IF(OR(U2="Jury Duty,U2="Waiting",U2="Personal Absence,U2="military",U2="Absence"),"other",U2)

 

[srizki]

Thank you.

 

[taurean]

To make your original formula work you need to make some minor changes to it:
=IF(U2={"Jury Duty","Waiting","Personal Absence","military","Absence"},"Other",U2)
And then CSE (Control + Shift + Enter) it!!

 

[marka87uk]

To do it the way you originally tried you'd need to do:

=IF(U2={"Jury Duty","Waiting","Personal Absence","military","Absence"},"True","False")

 

[marka87uk]

To make your original formula work you need to make some minor changes to it:
=IF(U2={"Jury Duty","Waiting","Personal Absence","military","Absence"},"Other",U2)
And then CSE (Control + Shift + Enter) it!!

Beaten to it! Out of interest is the CSE always needed because I can enter a formula like that normally and it seems to work OK?

 

[srizki]

Beaten to it! Out of interest is the CSE always needed because I can enter a formula like that normally and it seems to work OK?

If you read Aladin's comments, he says try not to use CSE, even though it works like magic, but for long spreadsheet like the one I have 50,000 rows, it slows down very considerably.