# Thread: More Calculations (Excel 2002 XP)

1. ## 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. ## Re: More Calculations (Excel 2002 XP)

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

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•