Working backwards from a tiered pricing rate

[tacester]

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>
0	500	0	500	1.00	1.00
500	900	500	1000	0.80	-0.20
900	1250	1000	1500	0.70	-0.10
1250	1850	1500	2500	0.60	-0.10
1850	1e+99	2500	1e+99	0.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]

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]

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]

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]

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....