Excel IF statements?

happy_2008 · 1 Apr 2015 at 13:44

Need some help on if statements if any peeps can advise. I'm trying to write a if statements to show the following

IF cell a1 is

Gbp=gbp
Eur = eur
All other currency = OTH

Ian_Eb · 1 Apr 2015 at 13:46

=IF(A1="gbp","gpb",IF(A1="Eur","eur","OTH"))

This isn't case sensitive so should work with any input for GPB(or Gbp etc)

dl8860 · 1 Apr 2015 at 13:47

Blew my mind when I realised you could do nested if statements like this

tom_e · 1 Apr 2015 at 13:48

IF (a1 = "Gbp", "gbp" , IF(a1 ="Eur", "eur" , "OTH"))

Beaten like a ginger step child!

dl8860 said:
Blew my mind when I realised you could do nested if statements like this

They really blow your mind when someones gotten over excited with them and you've then got to try and figure out what's going on.

oweneades · 1 Apr 2015 at 16:23

dl8860 said:
Blew my mind when I realised you could do nested if statements like this

Yup up to 6 unless Excel 2010 + has added support for more. It can be very messy when you start adding vlookups and DSUMs to the equation as well..

Lifelong · 1 Apr 2015 at 16:26

=IF(A1="GBP","GBP",IF(A1="Eur","Eur","OTH"))

lol, that'll teach me for leaving a tab open for a few hours.

kaiowas · 1 Apr 2015 at 17:14

Just to be different, and easier to expand in future without creating a nested monster:

=IF(OR(A1="gbp",A1="eur"),LOWER(A1),"OTH")

Ryan-3 · 1 Apr 2015 at 17:30

=IF(Ryan-3="Cared to listen",[ANSWER CORRECTLY],[Instead this failure is shown..]

Not to derail, but they've added some useful features to office, but in the process have made their already included features worse or more of a pain to use.

semi-pro waster · 1 Apr 2015 at 18:32

kaiowas said:
Just to be different, and easier to expand in future without creating a nested monster:

=IF(OR(A1="gbp",A1="eur"),LOWER(A1),"OTH")

Slight expansion on that to do a bit of tidying up (which wasn't actually asked for) but if you went with =IF(A1="","",(IF(OR(A1="gbp",A1="eur"),LOWER(A1),"OTH"))) then where nothing is entered in the cell then the formula will return a blank. It looks a bit neater if you're copying the formula down the spreadsheet as otherwise you'll end up with "OTH" all the way down when really there's nothing to be displayed. Makes no functional difference but it might appeal if you're obsessive about keeping it looking clean.

Grrrrr · 1 Apr 2015 at 21:31

Since we're getting down into ultra pedantic detail, the real Excel pros write OR functions like this

Code:

OR(A1={"eur","gbp"})