IF formula

[danielryle]

Hi all,

Relative beginner here, sorry!

I currently have a formula that is producing the result I need,

'=IF(E10>=30,"3",IF(E10>=23,"2",IF(E10<23,"1", "")))'

However if the cell contains text or is blank as shown above, I would like the resulting cell to be empty. How do I change my current formula to achieve this.

Many thanks,

Dan

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(or(e10="",istext(e10)),"",if(E10>=30,"3",IF(E10>=23,"2",IF(E10<23,"1", ""))))

 

[danielryle]

Perfect, thanks you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jtakw]

Hi,

A couple more ways, and I'm thinking you may not want quotes in your results, the quotes turns the results (3, 2, 1) into text:

Book3.xlsx
	E	F	G
10	35	3	3
11	25	2	2
12	20	1	1
13	a
14	b
15
16	d
17	30	3	3
18	e
Sheet1026

Cell Formulas
Range	Formula
F10:F18	F10	=IF(COUNT(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")
G10:G18	G10	=IF(ISNUMBER(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")

 

[danielryle]

Hi,

A couple more ways, and I'm thinking you may not want quotes in your results, the quotes turns the results (3, 2, 1) into text:

Book3.xlsx
	E	F	G
10	35	3	3
11	25	2	2
12	20	1	1
13	a
14	b
15
16	d
17	30	3	3
18	e
Sheet1026

Cell Formulas
Range	Formula
F10:F18	F10	=IF(COUNT(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")
G10:G18	G10	=IF(ISNUMBER(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")

You solved the issue I was just about to come back with!

 

[jtakw]

You solved the issue I was just about to come back with!

Good to know, thanks for the feedback.

One more way, in case you're interested:

Book3.xlsx
	E	F	G	H
10	35	3	3	3
11	25	2	2	2
12	20	1	1	1
13	a
14	b
15
16	d
17	30	3	3	3
18	e
Sheet1026

Cell Formulas
Range	Formula
F10:F18	F10	=IF(COUNT(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")
G10:G18	G10	=IF(ISNUMBER(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")
H10:H18	H10	=IF(N(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")

 

[danielryle]

Good to know, thanks for the feedback.

One more way, in case you're interested:

Book3.xlsx
	E	F	G	H
10	35	3	3	3
11	25	2	2	2
12	20	1	1	1
13	a
14	b
15
16	d
17	30	3	3	3
18	e
Sheet1026

Cell Formulas
Range	Formula
F10:F18	F10	=IF(COUNT(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")
G10:G18	G10	=IF(ISNUMBER(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")
H10:H18	H10	=IF(N(E10),IF(E10>=30,3,IF(E10>=23,2,1)),"")

New question: How would one write a formula for a cell that would say: If E10='word1' then F10='number1' and if E10='word2' then F10='number2' and similarly I would need to use the numbers in a subsequent calculation so would need to be a number and not text.

 

[Fluff]

If you need the result to be numeric, then you cannot have text in the cell. However you could use a custom cell format to make it look as though the word "number" is there.

 

[jtakw]

There are many ways to do this, from Nested IFs, VLOOKUP, INDEX/MATCH, LOOKUP, etc.
Depending on, for example, How many different words you have, and the values you want returned.

Note: Mods may suggest you open a new thread for your new question.