What does this formula mean?

[BMil8]

Hello,
I can’t seem to figure out a formula written by the person who had my job before me. The formula is used during our quarter end commissions calculation and the result is in the form of a percent of a payout the employee will actually received based on their sales performance. The formula is in cell G:4 and is as follows:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
=INDEX($C$2:$C$6,MATCH(E4,$B$2:$B$6,1),1) + (E4-INDEX($B$2:$B$6, MATCH(E4,$B$2:$B$6,1),1))*INDEX($D$2:$D$6,MATCH(E4,$B$2:$B$6,1),1)
<o> </o>
<o> </o>
In cells B1:D6 is the following table:
<o> </o>
<TABLE style="MARGIN: auto auto auto 4.8pt; WIDTH: 175pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0pt 5.4pt 0pt 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=292><TBODY><TR style="HEIGHT: 14.4pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 83pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0pt" vAlign=bottom width=138 noWrap>Goal Achievement<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 43pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0pt" vAlign=bottom width=72 noWrap>% of goal<o></o>
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 49pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0pt" vAlign=bottom width=82 noWrap></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 83pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=138 noWrap>

0%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 43pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=72 noWrap>

0%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 49pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=82 noWrap>

0<o></o>​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 83pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=138 noWrap>

70%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 43pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=72 noWrap>

40%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 49pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=82 noWrap>

2<o></o>​

</TD></TR><TR style="HEIGHT: 14.4pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 83pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=138 noWrap>

95%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 43pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=72 noWrap>

95%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 49pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=82 noWrap>

3<o></o>​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 83pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=138 noWrap>

100%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 43pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=72 noWrap>

100%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 49pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=82 noWrap>

5<o></o>​

</TD></TR><TR style="HEIGHT: 14.4pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 83pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=138 noWrap>

1000%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 43pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=72 noWrap>

9999%<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 49pt; PADDING-RIGHT: 5.4pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0pt" vAlign=bottom width=82 noWrap>

3<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>
This is all the information I knowingly have. Can anybody tell me what is happening here?

Thank you,
Brian

 

[Jonmo1]

OK, we need to know what is in E4.

Let's assume E4 = 50% for now..

This part is repeated 3 times in the formula
MATCH(E4,$B$2:$B$6,1)

=INDEX($C$2:$C$6,MATCH(E4,$B$2:$B$6,1),1) + (E4-INDEX($B$2:$B$6, MATCH(E4,$B$2:$B$6,1),1))*INDEX($D$2:$D$6,MATCH(E4,$B$2:$B$6,1),1)

So what it does, it searches B2:B6 for the closest match to E4
If no exact match is found, it then uses the Largest # that is less than E4
so in this case case, that is 40%.
Match then returns the position # within the array B2:B6, where 40% is found.
40% is in the 2nd postion (B3)
so
MATCH(E4,$B$2:$B$6,1) = 2

Now we'll replace all 3 instances of the match with 2

=INDEX($C$2:$C$6,2,1) + (E4-INDEX($B$2:$B$6, 2,1))*INDEX($D$2:$D$6,2,1)

Now, Index is a very simple yet very powerfull tool.
The syntax is
=INDEX(array, row#, column #)

we have 3 index formulas in there
INDEX($C$2:$C$6,2,1) - the 2nd row in the 1st column of C2:C6 = 40%
INDEX($B$2:$B$6, 2,1) - the 2nd row in the 1st column of B2:B6 = 70%
INDEX($D$2:$D$6,2,1) - the 2nd row in the 1st column of D2:D6 = 2

so the formula breaks down to
=40% + (E4-70%)*2

E4 = 50% as stated in the beginning
=40% + (50%-70%)*2

50%-70% = -20%
so
=40% + (-20%)*2

-20% * 2 = -40%
so
=40% + -40% = 0




Now that's what the formula does...
Making sense out of it's purpose is up to you.


Hope that helps.

 

[BMil8]

It's a great start. Thanks for the quick response! I should be able to make sense out of it if i stare at it long enough.

 

[Swift_74d]

=INDEX($C$2:$C$6,MATCH(E4,$B$2:$B$6,1),1) + (E4-INDEX($B$2:$B$6, MATCH(E4,$B$2:$B$6,1),1))*INDEX($D$2:$D$6,MATCH(E4,$B$2:$B$6,1),1)

So what it does, it searches B2:B6 for the closest match to E4
If no exact match is found, it then uses the Largest # that is less than E4
so in this case case, that is 40%.
Match then returns the position # within the array B2:B6, where 40% is found.
40% is in the 2nd postion (B3)
so
MATCH(E4,$B$2:$B$6,1) = 2

Not that i'm trying to correct you, but understand myself...wouldn't the Largest # in b2:b6 that is less than e4 (50%) be 0%, thus position 1? How does it jump over to column c?

 

[Jonmo1]

To the best of my knowledge...
40% is less than 50%
AND
40% is larger than 0%

Not sure I understand the 2nd question...how does it "jump" over to column c ?

 

[Jonmo1]

It's a great start. Thanks for the quick response! I should be able to make sense out of it if i stare at it long enough.

Glad to help, thanks for the feedback.

 

[Jonmo1]

Not that i'm trying to correct you, but understand myself...wouldn't the Largest # in b2:b6 that is less than e4 (50%) be 0%, thus position 1? How does it jump over to column c?

OK, I see it.
A bit of dislexia I guess.

I was seeing the 2nd column of the OP's posted table.
Not recognizing it began in column b...

 

[Jonmo1]

So after adjusting my explaination to reflect the correct columns....
Post #2 should read like

Let's assume E4 = 98%

This part is repeated 3 times in the formula
MATCH(E4,$B$2:$B$6,1)

=INDEX($C$2:$C$6,MATCH(E4,$B$2:$B$6,1),1) + (E4-INDEX($B$2:$B$6, MATCH(E4,$B$2:$B$6,1),1))*INDEX($D$2:$D$6,MATCH(E4,$B$2:$B$6,1),1)

So what it does, it searches B2:B6 for the closest match to E4
If no exact match is found, it then uses the Largest # that is less than E4
so in this case case, that is 95%.
Match then returns the position # within the array B2:B6, where 95% is found.
95% is in the 3rd postion (B4)
so
MATCH(E4,$B$2:$B$6,1) = 3

Now we'll replace all 3 instances of the match with 3
=INDEX($C$2:$C$6,3,1) + (E4-INDEX($B$2:$B$6, 3,1))*INDEX($D$2:$D$6,3,1)


Now, Index is a very simple yet very powerfull tool.
The syntax is
=INDEX(array, row#, column #)

we have 3 index formulas in there
INDEX($C$2:$C$6,3,1) - the 3rd row in the 1st column of C2:C6 = 95%
INDEX($B$2:$B$6, 3,1) - the 3rd row in the 1st column of B2:B6 = 95%
INDEX($D$2:$D$6,3,1) - the 3rd row in the 1st column of D2:D6 = 3

so the formula breaks down to
=95% + (E4 - 95%)*3

E4 = 98% as stated in the beginning
=95% + (98% - 95%)*3

98% - 95% = 3%
so
=95% + (3%)*3

3% * 3 = 6%
so
=3%+6%

Formula results in 9%

 

[Jonmo1]

It really must be Monday....

3% * 3 = 6%
so
=3%+6%

Formula results in 9%

should be

3% * 3 = 9%
so
=3%+9%

Formula results in 12%

 

[BMil8]

When I plug in 98% in E4, I get 104% as a result.