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
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")))
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")))