looking for a formula please.

[john]

i'm a caterer. i keep track of parties that are picked up.
in my workbook, in one column ( column E), i enter the number of people in
the party. i would like a column ( say column L) to calculate how many
bottles of dressing i use for that party . ( without having to manually enter
each amount on each row.) i track this over the course of a year going back
10 years.
i have over 5000 rows in a year.

if the party is between 20 & 40 i use 2 bottles
if it's between 40 & 60 i use 4 bottles
if it's between 60 & 100 i use 8 bottles

is there a (less than, greater than formula the would work on what ever
amount i enter, beeing 20 or 100, that whould automaticly calculate that if
E2 is 20 then L2 is 2, or if E2 is 61 then L2 will show 4?

thank you!

 

[Mike]

=IF(E2<41,2,IF(E2<61,4,IF(E2<100,8,0)))

 

[FSt1]

hi
try this....
=IF(AND(D6>=20,D6<=40),2,IF(AND(D6>=41,D6<=60),4,IF(AND(D6>=61,D6<=100),8,"out of range")))

watch out. formula wraped. suppose to be all one line.

regards
FSt1

 

[Max]

Another one, using a vlookup with a hardcoded reference array :
In L2: =IF(E2="","",VLOOKUP(E2,{20,2;41,4;61,8},2))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[Sheeloo]

Enter this in L1 (change L1 to L2 ande E1 to E2 if you have a header row)

=LOOKUP(E1,{0,20,40,60},{0,2,4,8})
and copy down

This will give you 0 for 0-19, 2 for 20-39, 4 for 40-59 and 8 for 60+

 

[Harlan Grove]

if the party is between 20 & 40 i use 2 bottles
if it's between 40 & 60 i use 4 bottles
if it's between 60 & 100 i use 8 bottles

....

=LOOKUP(NumberOfPeople,{0;20;40;60;100},{"why bother";2;4;8;"Whoo
hoo!"})

 

[Simon Lloyd]

Try this:
=IF(AND(E1>=20,E1<=40),2,IF(AND(E1>40,E1<=60),4,IF(AND(E1>60,E1<=100),8,"")))


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)