Excel - Convert Number to Text Based on Conditions-CodePudding

How do I convert a number to Text in Excel based on what the number is.

For eg, I have the following logic to be implemented:

pob > 1 ? "Strong Buy" : pob > 0 ? "Buy" : pob < -1 ? "Strong Sell" : pob < 0 ? "Sell" : "Neutral"

Column A has values 0, 1, 2, -1 In Column B, I have to replace these numbers with the following words.

0 = Neutral
1 = Buy
2 = Strong Buy
-1 = Sell
-2 = Strong Sell

How do I do this in Excel?

CodePudding user response：

XLOOKUP() could be a perfect function for this case. Try-

=XLOOKUP(A1,{-2,-1,0,1,2},{"Strong Sell","Sell","Neutral","Buy","Strong Buy"},"",-1)

CodePudding user response：

This is another way of doing it. Put in cell: B1 the following formula:

=LET(set, A1:A14, CONV, LAMBDA(x, SWITCH(x, -2, "Strong Sell",-1, "Sell", 
  0, "Neutral", 1, "Buy",2, "Strong Buy", "CASE NOT DEFINED")),
 MAP(set, LAMBDA(x, CONV(x))))

Using SWITCH via user LAMBDA function CONV and MAP function, it doesn't require to drag-down the formula. The result is returned in one array. In case the number was not defined it returns: CASE NOT DEFINED. The LET function is used for easier reading and composition.

Here is the output using the sample data provided by @Harun24hr: