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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Cost From (>=)[/TD]
[TD]Cost To (<)[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69"]Clicks (>=)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Clicks To (<) [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD="class: xl65, width: 49"]CPC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65"]Diff. Rate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]900[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]0.80[/TD]
[TD]-0.20[/TD]
[/TR]
[TR]
[TD]900[/TD]
[TD]1250[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD]0.70[/TD]
[TD]-0.10[/TD]
[/TR]
[TR]
[TD]1250[/TD]
[TD]1850[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]0.60[/TD]
[TD]-0.10[/TD]
[/TR]
[TR]
[TD]1850[/TD]
[TD]1e+99[/TD]
[TD]2500[/TD]
[TD]1e+99[/TD]
[TD]0.40[/TD]
[TD]-0.20[/TD]
[/TR]
</tbody>[/TABLE]

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