Vlookup question

[mgfx]

In the folowing table my profesor used the following formula to get the cost of any number of ads

<table style="border-collapse: collapse; width: 389pt;" width="518" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 59pt;" width="79"> <col style="width: 102pt;" width="136"> <col style="width: 48pt;" width="64"> <col style="width: 67pt;" width="89"> <col style="width: 65pt;" width="86"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td> <td style="width: 59pt;" width="79">
</td> <td style="width: 102pt;" width="136">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 67pt;" width="89">
</td> <td class="xl67" style="width: 65pt;" width="86">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17"># of ads</td> <td>Unit cost</td> <td>Cost up to breakpoint</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td class="xl65"> $12.000,00 </td> <td align="right">0</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">6</td> <td class="xl65"> $11.000,00 </td> <td class="xl66"> $ 60.000,00 </td> <td>
</td> <td>Ads bought</td> <td>Cost</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">11</td> <td class="xl65"> $10.000,00 </td> <td class="xl66"> $ 115.000,00 </td> <td>
</td> <td align="center">22</td> <td class="xl65"> $233.000,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">21</td> <td class="xl65"> $ 9.000,00 </td> <td class="xl66"> $ 215.000,00 </td> <td>
</td> <td align="center">3</td> <td class="xl65" align="center"> $ 36.000,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl66"> $ 26.000,00 </td> <td>
</td> <td align="center">7</td> <td class="xl65"> $ 82.000,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="center">13</td> <td class="xl65"> $145.000,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="center">8</td> <td class="xl65"> $ 93.000,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="center">4</td> <td class="xl65"> $ 48.000,00 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td align="center">10</td> <td class="xl65"> $115.000,00 </td> </tr> </tbody></table>
He used this formula to look for the cost of any number of ads bought:
=VLOOKUP(F7;cost;3)+(F7--1-VLOOKUP(F7;cost;1))*VLOOKUP(F7;cost;2)
where "cost" is the range name for the first 3 columns. The answer to the problem is in the last two columns.

my question es why he uses F7--1? I can see is the right answer because you can get to it using the "normal" way by multiplying each cost with its corresponding cost.

If anyone can help I will really appreciate it

thx

 

[Sandeep Warrier]

Hi and welcome to the Board!

F7--1 is equivalent to F7+1

I do not, however, understand why this is so.

For 22 ads bought:
According to the table, 21 ads cost $215,000. A single unit cost in this band is $9,000. So 22 ads should ideally cost $215,000 + $9,000 = $224,000

However the result is $215,000 + $9,000 * 2... which should ideally be the cost for 23 ads.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[mgfx]

Thanks sandeep.warrier

In this case the cost table is this:

From/To Cost

1 to 5 ads 12.000
6 to 10 ads 11.000
11 to 20 ads 10.000
21 or more 9.000

So the total cost for 13 ads (the normal way) should be:

5 x 12.000=60.000
5 x 11.000=55.000
3 x 10.000=30.000

TOTAL COST FOR 13 ADS=145.000 (AS SHOWN IN THE TABLE OF RESULTS)

=VLOOKUP(F7;cost;3)+(F7--1-VLOOKUP(F7;cost;1))*VLOOKUP(F7;cost;2)
where "cost" is the range name for the first 3 columns and F7 is the number of ads you are looking for (in this case 13)

YOU CAN SEE THAT THE ANSWER IS CORRECT LOOKING FROM THE PERSPECTIVE ABOVE (AND IN THE 4th AND 5th COLUMN)

F7+1 (13+1) IN THIS EXERCISE

PLEAAAAAAASE

 

[Sandeep Warrier]

Ok...

In that case, the formula is pretty straight forward.

=VLOOKUP(F7;cost;3)+(F7--1-VLOOKUP(F7;cost;1))*VLOOKUP(F7;cost;2)

Breaking it into parts:
1) VLOOKUP(F7;cost;3) - Here the last option for VLOOKUP is missing and hence is taken as 1 (or TRUE) by default. This means that if the lookup value is not found, VLOOKUP will look for the next lower number. F7 is 13 and since the cost table does not contain 13, VLOOKUP looks up the next lower number which is 11 and will return the value from column 3 of cost which is $115,000. Now according to the logic you gave, this value would be the cost for 10 ads. 3 ads are pending.

2a) (F7--1-VLOOKUP(F7;cost;1)) - Following the logic from point 1, VLOOKUP will return 11. F7 returns 13. F7--1 will return 14 and then 14-11 will give 3, which is the pending number of ads.

2b) VLOOKUP(F7;cost;2) - Following the same VLOOKUP logic, this will return $10,000.

and hence (F7--1-VLOOKUP(F7;cost;1))*VLOOKUP(F7;cost;2) returns
3*$10,000 = $30,000.

The entire formula would then result in

$115,000 + $30,000 = $145,000 as seen in the result column.

I'm not sure why your prof used F7--1 instead of F7+1. Maybe has something to do with personal choice!!!

Hope this helps.

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[mgfx]

THANK YOU VERY MUCH! HELPED A LOT!!!