Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working on segments of data from within a list
I have a list of data which has the capacity to be say 5000 lines in length.
The simplified structure looks as follows: A B C D RefCode Price 1 Price2 Required 1 11111 200 20 =200/380*20 2 11111 200 60 =200/380*60 3 11111 200 100 =200/380*100 4 11111 200 200 =200/380*200 5 22222 100 20 6 22222 100 100 7 33333 500 500 =500/700*500 8 33333 500 200 =500/700*200 9 44444 150 150 10 44444 150 10 11 44444 150 20 12 44444 150 30 13 44444 150 150 The price in column B will always be the same for each entry in column A. What I would like to be able to do (without inserting a manual subtotal calculation under each RefCode segment) is to take the Price1 figure in column B for the Refcode grouping and divide it by the sum of the Price2 figures in column C to give a redistribution of the total price against the Price2 figures( Please see column D) Im probably straying into the realms of using a bit of VB coding to accomplish this which is where Im somewhat lost but if anyone has any ideas as to how I might automate this I would be very interested to hear. Thank you Gordon. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working on segments of data from within a list
Try this formula in D1 and copy down..
=B1/SUMIF(B:B,B1,C:C)*C1 If this post helps click Yes --------------- Jacob Skaria "Gordon" wrote: I have a list of data which has the capacity to be say 5000 lines in length. The simplified structure looks as follows: A B C D RefCode Price 1 Price2 Required 1 11111 200 20 =200/380*20 2 11111 200 60 =200/380*60 3 11111 200 100 =200/380*100 4 11111 200 200 =200/380*200 5 22222 100 20 6 22222 100 100 7 33333 500 500 =500/700*500 8 33333 500 200 =500/700*200 9 44444 150 150 10 44444 150 10 11 44444 150 20 12 44444 150 30 13 44444 150 150 The price in column B will always be the same for each entry in column A. What I would like to be able to do (without inserting a manual subtotal calculation under each RefCode segment) is to take the Price1 figure in column B for the Refcode grouping and divide it by the sum of the Price2 figures in column C to give a redistribution of the total price against the Price2 figures( Please see column D) Im probably straying into the realms of using a bit of VB coding to accomplish this which is where Im somewhat lost but if anyone has any ideas as to how I might automate this I would be very interested to hear. Thank you Gordon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indicating lines from data labels to pie chart segments | Charts and Charting in Excel | |||
Carrying out calculations on segments of data from within a workbo | Excel Worksheet Functions | |||
Data Validation List Not Working Correctly | Excel Discussion (Misc queries) | |||
How do i use Xlstat for clustering data into segments | Excel Programming | |||
Data Validation Dropdown List Not Working | Excel Discussion (Misc queries) |