Results 1 to 5 of 5

Thread: Vlookup ?

  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: Vlookup ?

    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?

    I"m on AIM - CanadaMike20
    ICQ - 38736924
    MSN - quilmrt@hotmail.com
    Email: michael@camptlc.com

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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    I almost have the solution for you, but I may have to wait until after lunch break.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    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 12:38.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    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. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay. Let me attach a file with what I did.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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