Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to applying a ratio (whilst keeping total the same!)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to applying a ratio (whilst keeping total the same!)
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to applying a ratio (whilst keeping total the same!)
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 "Sheeloo" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate whilst keeping formatting | Excel Discussion (Misc queries) | |||
Calculate a ratio of two numbers to the total | Excel Worksheet Functions | |||
Resize chart (Word 2007) while keeping H/W ratio? | Charts and Charting in Excel | |||
Keeping Chart Series Formats whilst sorting | Charts and Charting in Excel | |||
Right Filling A Formula Whilst Keeping A Static Column | Excel Discussion (Misc queries) |