Thread: Calculating cost from known percentages

Hi,

I have say a cost of £600.00 and a row of percentages adding up to 100%
which will remain static ie 5.5% May 6.00% June etc. Is there a simple formala that I can use to perform this function which will enter into a row/column next to the percentage the cost.

I am not an expert on Excel so would appreciate simple instructions.

Thanks

Alan

Hi,

Your post doesn't explain which month has what %. So bearing that in mind I've had to make some assumptions.

In A1 enter 01/01/2006 and format as "MMM".
In A2 enter =DATE(YEAR($A$1),MONTH($A$1)+ROW(A1),DAY(1)) and drag down to Dec. Format as A1

This gives you your 12 months.

Now in B1 enter

=CHOOSE(MONTH($A$1:$A$12),$C$1*3.5%,$C$1*4%,$C$1*4.5%,$C$1*5%,$C$1*5.5%,$C$1*6%,$C$1*6.5%,$C$1*7%,$C $1*7.5%,$C$1*8%,$C$1*8.5%,$C$1*9%)

In C1 enter your $600 cost.

basically the choose function returns a 1 for Jan which calcualtes $C$1 ($600 cost) * 3.5%.

So for May and June the formula part is $C$1*5.5%,$C$1*6%. Modify this part of the formula to get your 100%.

$C$1*3.5%,$C$1*4%,$C$1*4.5%,$C$1*5%,$C$1*5.5%,$C$1*6%,$C$1*6.5%,$C$1*7%,$C$1*7.5%,$C$1*8%,$C$1*8.5%, $C$1*9%

Hope this helps

VBA Noob