looking for a formula please.
 

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

looking for a formula please.
 

=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

looking for a formula please.
 

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

looking for a formula please.
 

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?

looking for a formula please.
 

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

looking for a formula please.
 

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

looking for a formula please.
 

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