Convert value in cell to show text

[nparsons75]

Hi, I need to show some text in a cell based on the value in another cell. This is the formula I am using which is working great.

=IF(F17=100,"Complete",IF(F17>0,"Active",IF(F17=0,"Pending","")))

However, I need to add a fourth criteria, F17=999,"On Hold"

When I try this it errors saying to many arguments for this function. Does anyone know a better way or a workaround.

Thank you.

 

[wideboydixon]

=IF(F17=999,"On Hold",IF(F17=100,"Complete",IF(F17>0,"Active",IF(F17=0,"Pending",""))))

WBD

 

[MARZIOTULLIO]

=IF(F17=100,"Complete",IF(AND(F17>0,F17<>999),"Active",IF(F17=0,"Pending",IF(F17=999,"ON HOLD",""))))

 

[Rick Rothstein]

I am not sure of your complete range of values, but perhaps something like this...

=IF(F17="","",LOOKUP(F17,{0,1,100,999},{"Pending","Active","Complete","On Hold"}))

 

[wideboydixon]

Hi Rick,

That looks risky and doesn't generate the same results as the original formula:

Excel 2016 (Windows) 32 bit
	F	G	H
17	-1	#N/A
18	0	Pending	Pending
19	1	Active	Active
20	2	Active	Active
21	100	Complete	Complete
22	101	Complete	Active
23	999	On Hold	On Hold
24	1000	On Hold	Active
Sheet1

Cell Formulas
Range	Formula
G17	=IF(F17="","",LOOKUP(F17,{0,1,100,999},{"Pending","Active","Complete","On Hold"}))
H17	=IF(F17=999,"On Hold",IF(F17=100,"Complete",IF(F17>0,"Active",IF(F17=0,"Pending",""))))

WBD

 

[nparsons75]

Hi, It seems to work for me, thanks Rick. I will also look at your solution WBD as maybe I will come up against an error later after further tests. So far though it looks good. Thank you

 

[Rick Rothstein]

Hi Rick,

That looks risky and doesn't generate the same results as the original formula:

Whether my formula works for the OP or not depends on what values can be used when the value is not 0, 100 or 999 (which is why I started my post by saying "I am not sure of your complete range of values"). Based on what the OP posted in Message #6 , it seems whatever restricted set of values are actually being used fall within the ranges I set up within my formula.

 

[wideboydixon]

Hey Rick Rothstein,

I wasn't trying to criticize your post and I apologize if that's how you saw it. I wanted to make the OP aware that it could potentially produce different results if the data wasn't as well-defined as it appeared to be. I've fallen into the trap of doing similar approaches before only for the OP to suddenly throw new data into the mix which invalidates my entire solution.

Greetings from the UK :wink:

WBD

 

[Rick Rothstein]

Hey Rick Rothstein,

I wasn't trying to criticize your post and I apologize if that's how you saw it. I wanted to make the OP aware that it could potentially produce different results if the data wasn't as well-defined as it appeared to be. I've fallen into the trap of doing similar approaches before only for the OP to suddenly throw new data into the mix which invalidates my entire solution.

I did not take it to be criticism. And I thought about the other values problem you raised when I developed my formula. It just didn't seem plausible to me to assume an Active designation (if I am remembering this correctly) would exist on the other side of markers for Complete or Pending or whatever; so I assumed they wouldn't and added my qualifying text "just in case". And yes, I know exactly what you mean about OPs throwing in "monkey wrenches" by telling us about conditions they did not mention initially.