CODEWITHSUNDEEP
excelif-statementnumbers
Blaž Čukelj
Blaž Čukelj

Reputation: 83

Convert number to string in Excel

I'm trying to do some transformation with numbers in excel. First i have that table: Excel Numbers

And as you can see, i have Random Digits, which is generated by using RANDBETWEEN. Now i want the Column Type, to be automatically Generated. So for example if Random Digits is:

I was already trying with IF function, but with if function i'm able to generate only 2 values and not 3.

Thank you for answers.

Upvotes: 2

Views: 679

Answers (4)

JimbobTheSailor
JimbobTheSailor

Reputation: 1623

The excel formula you are looking for is 

=IF(B1>100,"error",IF(B1>=81,"Poor",IF(B1>=36,"Fair",IF(B1>=1,"Good","error"))))

This will display the word "error" if you range is >100 or <1. Other answers have failed to address the cases where the number is >100 or <1, as the question specifically bounds the set of responses to be between 1 and 100.

The formula works as a nested if statement. In pseudo code the formula is equivalent to:

if(B1>100)
   then "Error"
Else if (B1>=81)
  then "Poor"
Else if (B1>=36)
  then "Fair"
Else if (B1>=1)
  then "Good"
else
  "Error"

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

INDEX and MATCH are a good way to avoid nesting lots of IF statements (generally to be avoided!):

=INDEX({"Good","Fair","Poor"},MATCH(B2,{0,36,81},1))

If you really wanted to use an IF statement, it would look like this:

=IF(B2<36,"Good",IF(B2<81,"Fair","Poor"))

Upvotes: 3

gian quiroz
gian quiroz

Reputation: 67

Use one IF inside another IF like this:

=if('From 1 - 35';'thing to do if is true';if('36 - 80';'thing to do if is true';'thing to do when is 81 - 100'))

Upvotes: 1

SomethingCool
SomethingCool

Reputation: 303

Nest the If so where you get the true value just output what you need but if its false then just write another if statement...

Upvotes: 1

Related Questions