# Working backwards from a tiered pricing rate

#### tacester

##### New Member
Hi,

I am building a tool that shows me the effective cost per click for a number of clicks based on some pricing tiers...

Cost From (>=)Cost To (<)
 Clicks (>=)

<tbody>
</tbody>
 Clicks To (<)

<tbody>
</tbody>
 CPC

<tbody>
</tbody>
 Diff. Rate

<tbody>
</tbody>
050005001.001.00
50090050010000.80-0.20
9001250100015000.70-0.10
12501850150025000.60-0.10
18501e+9925001e+990.40-0.20

<tbody>
</tbody>

I am successfully able to use sumproduct to work out my recommended budget based on the number of clicks I want to buy. Using the formula below, where the number of clicks is in B10 and the table above is stored in the "data" worksheet.

=SUMPRODUCT(--(\$B\$10>data!\$C\$2:\$C\$6),\$B\$10-data!\$C\$2:\$C\$6,data!\$F\$2:\$F\$6)

That works great.

But I've hit a brain wall when trying to work backwards from a set budget to work out the number of clicks I would receive based on the table above.

What is the most elegant way this can be achieved?

#### oldbrewer

##### Board Regular
it seems to me that 1 to 500 clicks cost 1 unit each, 501 to 1000 cost .9 units each, 1001 to 1500 clicks cost 5/6 units each, 1501 to 2500 cost 37/50 units each, but how to determine the cost for 2501 and above, maybe 0.7 ????

#### tacester

##### New Member
it seems to me that 1 to 500 clicks cost 1 unit each, 501 to 1000 cost .9 units each, 1001 to 1500 clicks cost 5/6 units each, 1501 to 2500 cost 37/50 units each, but how to determine the cost for 2501 and above, maybe 0.7 ????
That is not entirely true. E.g

600 clicks = 0.97
999 clicks = 0.90

The price is tiered so the more you buy the lower the effective cpc becomes.

Is there a way to use sumproduct in reverse (if that makes sense)?

#### tacester

##### New Member
SOLVED: Working backwards from a tiered pricing rate

I finally figured it out. I ended up using VLOOKUP to get the range and find the remainder and divide by the CPC to add on top.

B15 = Budget \$\$

=VLOOKUP(B15,data!\$A\$1:\$F\$6,3,TRUE)+((B15-VLOOKUP(B15,data!\$A\$1:\$F\$6,1,TRUE))/VLOOKUP(B15,data!\$A\$1:\$F\$6,5,TRUE))

#### oldbrewer

##### Board Regular
Re: SOLVED: Working backwards from a tiered pricing rate

if in the original post you has said that the first X clicks were y dollars, the next x2 clicks were y2 dollars and so on, it would have been much clearer and maybe easier to solve...I would have used a lookup table....