Working backwards from a tiered pricing rate

tacester

New Member
Joined
May 16, 2018
Messages
3
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
Joined
Apr 11, 2010
Messages
10,985
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
Joined
May 16, 2018
Messages
3
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
Joined
May 16, 2018
Messages
3
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
Joined
Apr 11, 2010
Messages
10,985
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....
 

Forum statistics

Threads
1,085,304
Messages
5,382,831
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top