Results 1 to 10 of 10

20050414, 21:54 #1
 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(C2D2,$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

20050414, 22:37 #2
 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...

20050414, 22:38 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050414, 22:55 #4
 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)

20050414, 23:02 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)

20050414, 23:20 #6
 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.

20050414, 23:36 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050414, 23:50 #8
 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

20050414, 23:57 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050415, 00:01 #10
 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