Problem with using istext in a nested IF function

[Ciarmccabe]

Hi,

I'm a novice excel user and have a problem with a formula I tried,

I have a worksheet and if cell a2 is blank then I want b2 to be blank. If a2 is text then I want certain text displayed in b2. However the text displayed in b2 depends on what is in b1, example if b1=5 and a2 has text then b2 says "five times", if b1=4 and a2 has text then b2 says "four times", if b1=3 and a2 has text the then b2 says "three times" etc. Again if there is no text in a2 then b2 stays blank, there will always be a number in b1 but it doesn't matter unless a2 has text in it.

I'm not sure how to write the formula, I've tried this one =IF(ISTEXT(a2),IF(b1=5,"five times",IF(b1=4,"four times",IF(b1=3,"three times",IF(b1=2,"twice",""))))))

This one kind of works but I get false displayed in b2 every time a2 is empty, I presume I making a mistake somewhere,

I'd be grateful if anyone could help
Thanks

[FDibbins]

Hi and welcome to the forum

You did not include a test for blank...
=if(A2="","",IF(ISTEXT(a2),IF(b1=5,"five times",IF(b1=4,"four times",IF(b1=3,"three times",IF(b1=2,"twice","")))))))

An alternative to this would be to create a small table with the value and the corresponding text in it. then you could use a simple vlookup() to pull in the text. Something like...
=if(A2="","",vlookup(A2,$C$2:$D$4,2,0))

where C2:D4 contains the value (2,3,4,5 etc) and the text (Twice, three etc)

[martindwilson]

although there are nested ifs


IF(B1=5,"five times",IF(B1=4,"four times",IF(B1=3,"three times",IF(B1=2,"twice","")))) is a stand alone and is complete and is used as the TRUE part of the formula
=IF(ISTEXT(A2),true,false)
so you need to include the false part

=IF(ISTEXT(A2),true,"")
then add in the TRUE bit

=IF(ISTEXT(A2),IF(B1=5,"five times",IF(B1=4,"four times",IF(B1=3,"three times",IF(B1=2,"twice","")))),"")
this will give blank if a2 isnt text
you could also use
=IF(ISTEXT(A2),IF(B1<2,"",CHOOSE(B1-1,"twice","three times","four times","five times")),"")

[Ciarmccabe]

Thanks Guys, both solutions worked! Much appreciated!

[FDibbins]

Happy to help and thanks for the feedback