1. Registered User
Join Date
Oct 2003
Posts
13

I learnt how to do this like 5 years ago, btu I forget now...

Say I have a reference table - for example
PARTICIPANTS PRICE
0-40 \$40
41-60 \$38
61-80 \$36
81 + \$34

so for the first 40 people, you pay \$40, for the next 20, you pay a discounted rate, the next twenty etc etc

And then I have a bunch of different groups....
Group A - 111 participants
Group B - 57 participants
Group C - 63 participants
etc

WHat formula would go in the total cost cel??
Does this make sense?
I think it's an at VLOOKUP? or something - a combinaiton of something else?

ICQ - 38736924
MSN - quilmrt@hotmail.com
Email: michael@camptlc.com

Pls get back to me ASAP - I'm at work!
Thanks everyone
Mike

2. Registered User
Join Date
Oct 2003
Posts
1,091
I almost have the solution for you, but I may have to wait until after lunch break.

3. Registered User
Join Date
Oct 2003
Posts
1,091
Okay, Put "Number" in A2 and "Price" in B2 "Total Cost" in C2 and "Formula" in D2

Then put the following values in each cell:

A3: 40
A4: 60
A5: 80

B3: 40
B4: 38
B5: 36
B6: 34

C3 will be the cell that you will change to determine cost. D3 will provide the answer.

Then put this formula in D3
=IF(C6<=A\$3,A\$3*C6,IF((C6>A\$3)*(C6<=A\$4),(A\$3*B\$3) +(C6-A\$3)*B\$4,IF((C6>A\$4)*(C6<=A\$5),(A\$3*B\$3)+((A\$4-A\$3)*B\$4)+(C6-A\$4)*B\$5,(A\$3*B\$3)+((A\$4-A\$3)*B\$4)+(A\$5-A\$4)*B\$5+(C6-A\$5)*B\$6)))

As you change C3, the formula will total the appropriate amounts and display in D3.
Last edited by shades; 11-14-03 at 11:38.

4. Registered User
Join Date
Oct 2003
Posts
13
Hmmm I did everything to your instructions, but it doesn't seem to work... hmmm
I appreciate all your hard work though!

5. Registered User
Join Date
Oct 2003
Posts
1,091
Okay. Let me attach a file with what I did.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•