Multiple IF Statements

[dracophi]

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

 

[Guest]

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.

 

[bplumhoff]

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

 

[Dracophi]

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