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

 

[danielryle]

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.

apologies i should probably clarify I wouldn't need the word 'number' there, just a numerical value

 

[danielryle]

apologies i should probably clarify I wouldn't need the word 'number' there, just a numerical value

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.

It would just be two possible answers so two words and then two subsequent numerical values depending on the answer

 

[Fluff]

In that case you need to give a better example of exactly what you have & what you want.
But as it is a significantly different question, then it needs a new thread. Thanks

 

[jtakw]

If there could only be 2 possible values in E10 (e.g. word1, word2), then F10 formula will do, otherwise, use G10 formula:

Book3.xlsx
	E	F	G
10	word	2
Sheet1026

Cell Formulas
Range	Formula
F10	F10	=IF(E10="word1",1,2)
G10	G10	=IF(E10="word1",1,IF(E10="word2",2,""))

 

[danielryle]

If there could only be 2 possible values in E10 (e.g. word1, word2), then F10 formula will do, otherwise, use G10 formula:

Book3.xlsx
	E	F	G
10	word	2
Sheet1026

Cell Formulas
Range	Formula
F10	F10	=IF(E10="word1",1,2)
G10	G10	=IF(E10="word1",1,IF(E10="word2",2,""))

Hi there,

I have posted another thread but linked to this original thread. If you have any ideas it would be much appreciated!

vLOOKUP combined with an adjacent columns total

Hi there, Would anyone be able to help with a formula I am struggling to work on. Attached is a spreadsheet I am making, there is a set list of media types in column D, I would need a formula to lookup column D and depending on what media type is present look at column G and total the number...

www.mrexcel.com

Thanks