Solved: Formula to allocate absolute values

[danesrood]

Dear All

I have a need to allocate a value which will change from month to month against the percentage loadings for each site. The allocated value can only be a whole number and the total amount of the allocated values must total the original value. In a perfect world I would prefer to use the 2 decimal places shown below but if it helps I could round up or down. All I am trying to achieve is a reasonably fair and accurate method of allocating the costs.

Value to be allocated say 34

Exeter 21.60%
London 16.99%
Glasgow 19.54%
Edinburgh 5.39%
Birmingham 21.10%
Bath 15.38%
Totals 100.00%

The values will change every month and the percentages annually.

Your help will be appreciated

Many thanks

[mdmackillop]

I don't know if you are talking about Spreadsheet or VBA calculation

If the former, simply use the names as constant variables eg
Const Exeter = 0.216
Const London = 0.1699

If in a spreadsheet, you can use range names for each town and set these equal to the value. Then =A1*Exeter etc.

[danesrood]

I was looking for a formula that would produce a reasonably accurate and fair whole number value for each building based on the percentage loading that must add up to the total to be allocated.

[mdmackillop]

Can you post a sample workbook? I don't understand what is required.

[danesrood]

Certainly.. file attached which I hope explains my need.

I have to say that the number to be allocated is usually less than 100 each month.

[mdmackillop]

Select D4: D8 and enter =ROUND($C4:$C8*D3,0) as an array formula (Control/Shift/Enter).
In D9 enter =D3-SUM(D4: D8)
Copy to the right.

[Aussiebear]

MD, Is it because of the "rounding" that you require the different formula in cell D9?

[RonMcK]

Spot on, he's forcing the last item to be whatever it needs to be so the column totals the original fixed amount. This prevents the total of the allocated amounts from occasionally being off by +1 or -1.

Cheers!

[mdmackillop]

As Ron says. You may wish to make Exeter or Birmingham the "odd one" as the correction value will be less significant in a higher percentage. ie 10 v
9 as opposed to 4 v 3

[danesrood]

mdmackillop

Thank you that will do fine.

As you say I can juggle around which one to use as the catch all.

My sincere thanks for taking the time

Regards

danesrood