Hi,

I understand the basics of Excel coding, however a shorter method for
my task has stumped me.
I need to create a worksheet where I can do the following:

Let's say we have 3001 people in line.

If there are 3000(+) people behind a guy, he gets 3 apples.
If there are 2000+ (but less than 3000) people behind a guy, he gets 2
apples.
etc. and so on.
The important values would be:
#people 3000 2500 2000 1500 1000 750 500 300
#apples 3 2.5 2 1.5 1 0.9 0.8 0.7

#people 200 100 50 10
#apples 0.6 0.5 0.25 0.1

I would have to set ranges for the values. ie. If you had 751 people
behind you, you would still get 0.9 apples.

I understand that Nested IF statements only go up to 7 values. I also
tried using
=VLOOKUP(Target!A2,List!$A$2:$B$14,2,FALSE)
Where the target had the actual position of a person in line, and List
had values for the # of people and # of apples given in Ax and Bx
respectively. However that only allowed me to find exact values, not
ranges, which i need.

The ratios and # of people in behind a person would be preferrably
easily adjusted, to see the effects of a change in value.

I was wondering if anyone had a solution to my problem?

Thanks

If you change the FALSE in your formula to TRUE, between 750 and 999 people
will give you 0.9, and 1000 to 1499 will give you 1.
--
Hth

Kassie Kasselman


" wrote:

Hi,

I understand the basics of Excel coding, however a shorter method for
my task has stumped me.
I need to create a worksheet where I can do the following:

Let's say we have 3001 people in line.

If there are 3000(+) people behind a guy, he gets 3 apples.
If there are 2000+ (but less than 3000) people behind a guy, he gets 2
apples.
etc. and so on.
The important values would be:
#people 3000 2500 2000 1500 1000 750 500 300
#apples 3 2.5 2 1.5 1 0.9 0.8 0.7

#people 200 100 50 10
#apples 0.6 0.5 0.25 0.1

I would have to set ranges for the values. ie. If you had 751 people
behind you, you would still get 0.9 apples.

I understand that Nested IF statements only go up to 7 values. I also
tried using
=VLOOKUP(Target!A2,List!$A$2:$B$14,2,FALSE)
Where the target had the actual position of a person in line, and List
had values for the # of people and # of apples given in Ax and Bx
respectively. However that only allowed me to find exact values, not
ranges, which i need.

The ratios and # of people in behind a person would be preferrably
easily adjusted, to see the effects of a change in value.

I was wondering if anyone had a solution to my problem?

Thanks

Hello,

Use LOOKUP:
=LOOKUP(A1,{0,10,50,100,200,...},{0.1,0.25,0.5,0.6 ,...})
You may want to write the vectors into ranges and let lookup refer to
those...

HTH,
Bernd

Both edits worked well! Thanks very much Kassie and Bernd!