Formula Problem

[JP]

I have $700,000 to spend on advertising for three different products.

My market share for the three products is 51.96%, 36.07% and 41.25%.

I want to apportion my 700K between the three products so that the
product with the lowest market share gets the most advertising
dollars, etc.

Using my data, I've been trying to devise a formula all night that
would do this, but I'm stumped.

I could use a hint...

 

[Pete_UK]

Here's one way of doing it - put 700,000 in A1 and put your 2
percentage shares in B1:B3 and then put this formula in C1:

=A$1*(1-B1/SUM(B$1:B$3))/2

Format as currency and copy into C2:C3 to give you:

$209,328.60
$252,347.60
$238,323.80

But, of course, there are other ways ..

Hope this helps.

Pete

 

[Bob Phillips]

What algorithm do you want to use to apportion the money with?

Using the market share numbers seems inappropriate, as the markets will
differ in size, so would you want to spend most of you budget on a product
in a small market, just because you have a tiny share of that (small)
market?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JP]

The markets are close in size. Region one is slightly biggerr than
region two, and two is slightly bigger than three. One market doesn't
dwarf the other.