Nested If Statements

[wirra]

Hi,

Below is the result I am looking for but I need a way to calculate the Order Type in column I based on the value in column F. There are 10 order types for column I so I can't use a nested IF statement with the limitation of 7. I have tried defining three name formulae as shown in other threads but can't get it to work. Examples of my efforts are shown below.

Can anyone make a suggestion/formula how to calculate column I? I am using Excel 2003 under Windows XP.

Thanks

Bob

Review of GM Sales Orders Template Graham.xls
	B	C	D	E	F	G	H	I
1	OrderNo.	ProcessDate	OrderCost	OrderValueinclGST	OrderValuelessGST	GP%	Territory	OrderType
2	551428	31-Jul-08	$0.00	$0.00	$0.00	0.00	ZILL	1.Lessthan$10
3	434327	16-Apr-08	$38.95	$62.63	$56.94	31.59	ZILL	4.Between$50and$100
4	436004	4-Apr-08	$237.11	$278.22	$252.93	6.25	ZILL	6.Between$200and$300
5	436005	22-Apr-08	$206.08	$237.60	$216.00	4.59	ZILL	6.Between$200and$300
6	436006	6-May-08	$237.11	$278.22	$252.93	6.25	ZILL	6.Between$200and$300
7	439029	1-Jul-08	$3.19	$5.40	$4.91	35.03	ZILL	1.Lessthan$10
8	441527	4-Apr-08	$413.15	$536.64	$487.85	15.31	ZILL	8.Between$400and$500
9	441528	22-Apr-08	$413.01	$536.64	$487.85	15.34	ZILL	8.Between$400and$500
10	441529	8-May-08	$413.10	$536.64	$487.85	15.32	ZILL	8.Between$400and$500
11	441530	21-May-08	$413.10	$536.64	$487.85	15.32	ZILL	8.Between$400and$500
12	441531	4-Jun-08	$413.13	$536.64	$487.85	15.32	ZILL	8.Between$400and$500
13	441532	17-Jun-08	$415.57	$536.64	$487.85	14.82	ZILL	8.Between$400and$500
14	447074	4-Apr-08	$102.00	$0.00	$0.00	-10200.00	ZILL	1.Lessthan$10
15	450740	8-Apr-08	$1,454.78	$2,407.91	$2,189.01	33.54	ZILL	91.Greaterthan$1000
sale_sord2

criteria1

=IF($F$2<10,"1. Less than $10",IF(AND($F$2>=10,$F$2<20),"2. Between $10 and $20",IF(AND($F$2>=20,$F$2<50),"3. Between $20 and $50",IF(AND($F$2>=50,$F$2<100),"4. Between $50 and $100",FALSE))))

criteria2

=IF(AND($F$2>=100,$F$2<200),"5. Between $100 and $200",IF(AND($F$2>=200,$F$2<300),"6. Between $200 and $300",IF(AND($F$2>=300,$F$2<400),"7. Between $300 and $400",FALSE)))

criteria3

=IF(AND($F$2>=400,$F$2<500),"8. Between $400 and $500",IF(AND($F$2>=500,$F$2<1000),"9. Between $500 and $1000",IF($F$2>=1000,"91. Greater than $1000","Check")))

 

[Jonmo1]

Try using lookup...

Put your ranges and results in a table on your sheet, like

M1 = 0
N1 = "Less Than 10"
M2 = 10
N2 = "Between 10 and 49"
M3 = 50
N3 = "Between 50 and 99"
M4 = 100
N4 = "100 or Greater"


Something like that

Then your formula is

=LOOKUP(F2,M1:N4)

 

[wirra]

Thanks jonmo1. It works a treat. It's easy when you know how.

Cheers

Bob

 

[Jonmo1]

No problem.

Just for conversational reasons, the nested if function can be handy.
But you don't need to use the AND's in this situation.

For example

AND($F$2>=10,$F$2<20)

The nested if's are read from left to right, the First one to be true is used, the rest ignored.

So you don't need to test if F2 is Greater than or Equal to 10. Because if it wasn't, the previous IF (less than 10) would have been true. So you only need to test if <20.

Then the AND($F$2>=20,$F$2<50),
you don't need to test for Greater than or Equal to 20. Because if it wasn't, one of the previous 2 IF's would have been true.

Etc..

As long as the logical order makes sense. When testing for Less than, go from lowest to hightest (left to right),
If testing for Greater than, go from Highest to lowest (left to right).

So this would work in place of your first formula...

=IF($F$2<10,"1. Less than $10",IF($F$2<20,"2. Between $10 and $20",IF($F$2<50,"3. Between $20 and $50",IF($F$2<100,"4. Between $50 and $100",FALSE))))

 

[wirra]

jonmo1,

Thanks for the extra explanation. It is much appreciated.
My staff would be impressed, I keep telling them about saving keystrokes. lol

Bob