How to applying a ratio (whilst keeping total the same!)

[kippers]

Hi,

I currently have a range of tasks performed by team of people involving a
number of different stock items. I know that a few of these tasks take
longer for certain stock items. I have the total time taken for each process
per stock item but need to apply the weighting (and have the time taken for
the other items adjusted). I have been given the totals as actual figures
(so these cannot change) and I therefore need the table to recalculate the
rest of the figures when a ratio is applied to one figure. I have provided
an example below (random figures so I know it's not realistic !)

Purchase Process Deliver Invoice
Item1 £27.2 £156.5 £21.4 £20.2
Item2 £13.2 £75.8 £32.5 £12.1
Item4 £112.1 £644.6 £112.1 £32.1
Item5 £29.0 £32.6 £233.4 £110.4

TOTAL £181.5 £909.5 £399.4 £174.8

For example, if I know that it costs twice as much to deliver Item4 (than to
deliver Item1, Item2 or Item3), I need to be able to apply the ratio to this
figure in the table and have the table automatically update the rest of the
column (whilst keeping the TOTAL amount the same as this is the actual
figure).

Amy help would be gratefully received!

Rudyard

 

[Sheeloo]

Suppose you decide that for Purchase the ratios are 3/10, 2/10, 1/10 and 4/10
then you can enter these numbers in Col E2-E5 and use this formula in
B2
=$B$6*E2 and copy down
(assuming total is in B6)

You can enter ratios in Col F, G,... for other activites and use similar
formulas.

 

[kippers]

Thanks Sheeloo,

Only problem is that I don't know what the current ratios are for each item
and when I change one item (as it takes twice as long) the ratios will change
for the rest (I assume) so I want the table to automatically update the rest
of the data with the appropriate ratios if possible.

For example, on the table below, if I doubled the cost of delivering Item4,
the delivery cost for all other Items would need to adjust appropriately so
that the total remained the same.

Thanks,

Paul