help with nesting "IF" functions

[fobsista7]

Hello I am trying to use the if function but I am having so trouble coming up with the correct answers.

Here is a little background:

A company has the following compensation for its sales department:
A 5% commission on the actual selling price for cars sold at 98% or more of the asking price
A 3% commission on the actual selling price for cars sold at 95% or more (but less than 98%) of the asking price.
A 2% commission on the actual selling price for cars sold at 90% or more (but less than 95%) of the asking price
A 1 % commission on the actual selling price for cars sold at less than 90% of the asking price. The dealer will not go below 85% of his asking price.

With G4 as the percentage of asking price and F4 as the sales price this is the formula I came up with, however, it doesn't work as a percentage and when I used it as a paid dollar amount, only 2 of the answers were correct. Here is the formula I am using : =IF($G4=0.98,0.05*$F4,IF($G4>=0.95,0.03*$F4,IF($G4>=0.09,0.02*$F4,IF($G4>=0.85,0.01*$F4))))

What I need is the commission percentage and the commission paid.

Any help editing this would be greatly appreciated. Thank you in advance

 

[Aladin Akyurek]

Hello I am trying to use the if function but I am having so trouble coming up with the correct answers.

Here is a little background:

A company has the following compensation for its sales department:
A 5% commission on the actual selling price for cars sold at 98% or more of the asking price
A 3% commission on the actual selling price for cars sold at 95% or more (but less than 98%) of the asking price.
A 2% commission on the actual selling price for cars sold at 90% or more (but less than 95%) of the asking price
A 1 % commission on the actual selling price for cars sold at less than 90% of the asking price. The dealer will not go below 85% of his asking price.

With G4 as the percentage of asking price and F4 as the sales price this is the formula I came up with, however, it doesn't work as a percentage and when I used it as a paid dollar amount, only 2 of the answers were correct. Here is the formula I am using : =IF($G4=0.98,0.05*$F4,IF($G4>=0.95,0.03*$F4,IF($G4>=0.09,0.02*$F4,IF($G4>=0.85,0.01*$F4))))

What I need is the commission percentage and the commission paid.

Any help editing this would be greatly appreciated. Thank you in advance

Try something like:

=G4*LOOKUP(G4/F4,{0,0.85,0.9,0.95,0.98},{0,0.01,0.02,0.03,0.05})

where G4 houses an actual selling price and F4 an asking price.

 

[JokelesComedian]

This is how I would do it.

E1 F1 G1 H1
Asking Price Selling Price Sel% Comm%

Format G1 and H1 to percentage with no Decimal.

G1 =F1/E1

Thats the percentage of the selling price

Make a array like this off to the side (I would name it also but you dont have to)

85% 1%
90% 2%
95% 3%
98% 5%

Now use vlookup to get the percentage from the list

H1 =VLOOKUP(G1,I4:J7,2,TRUE)

I4:J7 is the array.

 

[fobsista7]

I tried using the Vlookup method as you suggested, however, since my values in G4 range from 85-100% I am getting "N/A" in a few of my cells. Is there something that I am missing?

 

[JokelesComedian]

You may need to lock in your range with $ signs or name your range.

Example: Array

I4:J7

$I$4:$J$7

 

[Aladin Akyurek]

I tried using the Vlookup method as you suggested, however, since my values in G4 range from 85-100% I am getting "N/A" in a few of my cells. Is there something that I am missing?

LOOKUP is the same thing which is also complete...

Either...

=G4*LOOKUP(G4/F4,{0,0.85,0.9,0.95,0.98},{0,0.01,0.02,0.03,0.05})

Or...

<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=174><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Sales %</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=110>Commission %</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>0.85</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.01</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>0.9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.02</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>0.95</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.03</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>0.98</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0.05</TD></TR></TBODY></TABLE>

which is entered in A1:B6, including the headers in a sheet called Admin. If so desired, you can set up also like this:

<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=174><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Sales %</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=110>Commission %</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>0%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>0%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>85%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>1%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>90%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>95%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>98%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>5%</TD></TR></TBODY></TABLE>

Select A2:A6, name the selected range SALESp using the Name Box on the Formula Bar. And, select B2:B6 and name this range in turn as COMMISSIONp.

Now you can invoke whichever you like:

=G4*LOOKUP(G4/F4,SALESp,COMMISSIONp)

=G4*INDEX(COMMISSIONp,MATCH(G4/F4,SALESp,1))

Name A2:B6 as CTable, then:

=G4*VLOOKUP(G4/F4,CTable,2,1)

 

[fobsista7]

Thank you for all of your help. I did need to use the $ with both the rows and the columns. Thank you again! Your help is greatly appreciated.