Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More Calculations (Excel 2002 XP)

    Hi again,
    I am trying to reference the difference between C2 and D2 to have E2 reflect the dealer's commission percentage. I have tried HLOOKUP, IF, SUM.
    I have the following formula that reflects a 5% but so does the rest of the dealers E3:E10.
    =IF(C2-D2,$B$16:$E$16)*($B$17:$E$17)
    Assuming I have done the column's B, C, D, F and G correctly getting this E comlumn corrected should make the spreadsheet balance.
    What am I doing wrong? (Yes, Hans, this is more homework. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Thanks so much everyone,
    LuceeLou

  2. #2
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Calculations (Excel 2002 XP)

    Thank you, let me give that a shot...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Calculations (Excel 2002 XP)

    The commission is dependent on the percentage the sales price is of the asking price. For example, if the asking price is $10000 and the sales price is $9600, the sales price as a percentage of the asking price is 9600/10000 = 0.96 or 96%. The commission for a percentage over 95% but less than 98% is 3% (see D1617).
    So what you have to do is calculate the sales price as a percentage of the asking price, and use that to look up the commission in B16:E17.

  4. #4
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Calculations (Excel 2002 XP)

    I am still coming up with a wrong percentage... <img src=/S/question.gif border=0 alt=question width=15 height=15>
    =VLOOKUP(D2/C2,$B$16:$E$17,TRUE)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Calculations (Excel 2002 XP)

    You should use HLOOKUP here, since you are looking for the search value in B16:E16, from left to right, then moving down. Moreover, you have omitted the 3rd argument (the row index)

  6. #6
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Calculations (Excel 2002 XP)

    =HLOOKUP(D2/C2,$B$16:$E$17,$B$16:$E$16,TRUE)
    I am getting a #REF! error... Please bear with me.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Calculations (Excel 2002 XP)

    The 3rd argument to HLOOKUP is not a range, but a single number. It specifies from which row of B16:E17 the return value should come. In this example, you want to return the commission percentage; this is in the second row of B16:E17, so the 3rd argument should be 2.

  8. #8
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Calculations (Excel 2002 XP)

    THANK YOU!!!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Ok, I got it. I am still learning the arguments and what they mean. I get it now that the final argument references the row. Thank you so much. I am eternally grateful.
    Here is the final formula I have and it reflects the correct percentages in my spreadsheet.
    =D2/C2*HLOOKUP(D2/C2,$B$16:$E$17,2,TRUE)
    I have learned so much thanks to you and this site. It is immeasureable!
    Are you willing to review what I have done?
    LuceeLou

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More Calculations (Excel 2002 XP)

    The formula =HLOOKUP(D2/C2,$B$16:$E$17,2,TRUE) calculates the correct percentage. There is no need to multiply this by D2/C2.

    You can look up functions such as HLOOKUP in the online help; it contains explanations for each argument, as well as examples.

  10. #10
    Lounger
    Join Date
    Apr 2005
    Location
    Mesa, Arizona, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Calculations (Excel 2002 XP)

    Thank you again Hans.
    You are so patient.
    LuceeLou

Posting Permissions

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