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!
--
thank you

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

"john" wrote:

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!
--
thank you

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

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

regards
FSt1

"john" wrote:

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!
--
thank you

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
---
"john" wrote:
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?

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+

"john" wrote:

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!
--
thank you

john wrote...
....
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!"})

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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=39042