Need help with if function for a range of data

[cbc818]

Hello everyone,
Long story short, I’m trying to make a little training program to teach people at my job about scheduling. I’m trying to write a formula to show somehow how many people they’ll need at each station based on projected attendance.
Let’s say O8 is where the attendance number is put in, I want P8 to read how many people are needed

Basically this:
0-200 people = 1 person per station
201- 300 = 2
301- 400 = 2-3
401-500 = 3-4
501+ = 4

I’ve tried the if function but it hasn’t worked for me. Anyone have any ideas?

 

[jproffer]

This is untested (no excel at home to try it on), but I'm still fairly confident it should work:

=If(O8<201,1,IF(AND(O8>200,O8<301),2,IF(AND(O8>300,O8<401),"2-3",IF(AND(O8>400,O8<501),"3-4",IF(O8>501,4,"ERROR")

I was basically "freehanding" that, so I may have missed a parenthesis or something, but barring that, it should work.

By the way, you should never see "ERROR", it's basically filler for that last false argument.

 

[xyga1998]

This will get you going:

IF(O8<201,1,(IF(AND(O8>200,O8<301),2,IF(AND(O8>300,O8<401),"2-3",IF(AND(O8>400,O8<501),"3-4",IF(O8>500,4,"Not Specified"))))))

 

[vcoolio]

...............or perhaps:-

=IF(AND(O8>0,O8<=200),1,IF(AND(O8>=201,O8<=300),2,IF(AND(O8>=301,O8<=400),"2-3",IF(AND(O8>=401,O8<=500),"3-4",IF(O8>501,4,"")))))

Cheerio,
vcoolio.