Nesting IF statements in Excel?

Feek · 22-11-2008, 15:38

I've worked out how to use a basic IF statement in Excel but I'm having problems getting my mind around working out multiple nested IFs.

I have a cell with a value in (Cell G13). If the value of that cell is greater than 4959.4 then I need the word 'one' displayed. That's easy.

Code:

=IF(G13>4959.4,"One","")

However, if the value of G13 is greater than 6323.2, the word 'two' should be displayed, and if it's above 8062.1 then the word 'three' displayed. Finally, if above 10279.2 then the word 'four' should be shown.

I've played around with multiple nesting using AND but I can't make it work. Can anyone help please?

Wellington · 22-11-2008, 15:54

On the basis that an IF statement is true or false it goes something along the lines of

If(Statement,"true","False") then you would need to use
=IF(G13>8062.1,"Three",(IF(G13>6323.2,"Two",(IF(G1 3>4959.4,"One",""))))

You would have to do so in this order as it will only read the first statement it it's "True". If you start with the lower value then a number greater than the value used in the statement would result in a true answer.

Burble · 22-11-2008, 16:00

Something like this should do the trick:

Code:

=IF(G13>10279.2,"Four",IF(G13>8062.1,"Three",IF(G13>6323.2,"Two",IF(G13>4959.4,"One","")))

Wellington · 22-11-2008, 16:04

that's the one

Feek · 22-11-2008, 16:08

Quote:

Originally Posted by Wellington

If(Statement,"true","False") then you would need to use
=IF(G13>8062.1,"Three",(IF(G13>6323.2,"Two",(IF(G1 3>4959.4,"One",""))))

That nearly worked, Excel told me it was wrong and offered a correction, thus:

Code:

=IF(G13>8062.1,"Three",(IF(G13>6323.2,"Two",(IF(G13>4959.4,"One","")))))

Quote:

Originally Posted by Burble

Code:

=IF(G13>10279.2,"Four",IF(G13>8062.1,"Three",IF(G13>6323.2,"Two",IF(G13>4959.4,"One","")))

That was also close! Again, it offered a correction thus:

Code:

=IF(G13>10279.2,"Four",IF(G13>8062.1,"Three",IF(G13>6323.2,"Two",IF(G13>4959.4,"One",""))))

I think that it was just a missing bracket in each case!

Thanks guys, exactly what I needed. Job is done.

Burble · 22-11-2008, 16:10

My copy and paste skills let me down a bit - there should been an extra ) at the end of mine.

Glad 'tis sorted though.

Wellington · 22-11-2008, 20:45

I just made mine up in my head

not surprised there was a ickle bit wrong...

anywho it works now...

22-11-2008, 15:38	#1
Feek ex SAS Join Date: Jun 2006 Location: JO01ou Posts: 10,062	Nesting IF statements in Excel? I've worked out how to use a basic IF statement in Excel but I'm having problems getting my mind around working out multiple nested IFs. I have a cell with a value in (Cell G13). If the value of that cell is greater than 4959.4 then I need the word 'one' displayed. That's easy. Code: =IF(G13>4959.4,"One","") However, if the value of G13 is greater than 6323.2, the word 'two' should be displayed, and if it's above 8062.1 then the word 'three' displayed. Finally, if above 10279.2 then the word 'four' should be shown. I've played around with multiple nesting using AND but I can't make it work. Can anyone help please? __________________

22-11-2008, 15:54	#2
Wellington Long Island Iced Tea Join Date: Sep 2007 Location: Cranham, Upminster, Essex Posts: 293	On the basis that an IF statement is true or false it goes something along the lines of If(Statement,"true","False") then you would need to use =IF(G13>8062.1,"Three",(IF(G13>6323.2,"Two",(IF(G1 3>4959.4,"One","")))) You would have to do so in this order as it will only read the first statement it it's "True". If you start with the lower value then a number greater than the value used in the statement would result in a true answer. __________________

22-11-2008, 16:00	#3
Burble Rocket Fuel Join Date: Jul 2006 Posts: 7,826	Something like this should do the trick: Code: =IF(G13>10279.2,"Four",IF(G13>8062.1,"Three",IF(G13>6323.2,"Two",IF(G13>4959.4,"One","")))

22-11-2008, 16:04	#4
Wellington Long Island Iced Tea Join Date: Sep 2007 Location: Cranham, Upminster, Essex Posts: 293	that's the one __________________

22-11-2008, 20:45	#7
Wellington Long Island Iced Tea Join Date: Sep 2007 Location: Cranham, Upminster, Essex Posts: 293	I just made mine up in my head not surprised there was a ickle bit wrong... anywho it works now... __________________

22-11-2008, 16:10	#6
Burble Rocket Fuel Join Date: Jul 2006 Posts: 7,826	My copy and paste skills let me down a bit - there should been an extra ) at the end of mine. Glad 'tis sorted though.