| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
running total by date (not sorted by date though...)
Is it possible to have a running total go by date while not sorted by date? I would like to change the date but not change the sorting so that the running total of units is accurate at that period in time. SO basically I can see how many units I have at anygiven time. I hope this makes sense. I am sure someone has had to do this before... Below are two example tables. Example (sorted by Account): Acct---Date------units-runnning total A-----5/01/2006---10-----25 B-----4/01/2006---10-----0 C-----4/15/2006---15-----10 D-----5/15/2006---20-----35 Final Count----55 Example (sorted by Date): Acct---Date------units-runnning total A-----4/01/2006---10-----0 B-----4/15/2006---15-----10 C-----5/01/2006---10-----25 D-----5/15/2006---20-----35 Final Count----55 Thank you!!! -- rainxking ------------------------------------------------------------------------ rainxking's Profile: http://www.excelforum.com/member.php...fo&userid=9431 View this thread: http://www.excelforum.com/showthread...hreadid=542183 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
running total by date (not sorted by date though...)
You may use the following array formula:
=SUM($C$1:$C$4*($B$1:$B$4<$B1)) Change the ranges as appropiate (here B column are dates, and C column units), and enter it with CRTL+SHIFT+ENTER Hope this helps, Miguel. "rainxking" wrote: Is it possible to have a running total go by date while not sorted by date? I would like to change the date but not change the sorting so that the running total of units is accurate at that period in time. SO basically I can see how many units I have at anygiven time. I hope this makes sense. I am sure someone has had to do this before... Below are two example tables. Example (sorted by Account): Acct---Date------units-runnning total A-----5/01/2006---10-----25 B-----4/01/2006---10-----0 C-----4/15/2006---15-----10 D-----5/15/2006---20-----35 Final Count----55 Example (sorted by Date): Acct---Date------units-runnning total A-----4/01/2006---10-----0 B-----4/15/2006---15-----10 C-----5/01/2006---10-----25 D-----5/15/2006---20-----35 Final Count----55 Thank you!!! -- rainxking ------------------------------------------------------------------------ rainxking's Profile: http://www.excelforum.com/member.php...fo&userid=9431 View this thread: http://www.excelforum.com/showthread...hreadid=542183 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
running total by date (not sorted by date though...)
This seems to work
IN B1 enter =SUMPRODUCT(--($B$1:$B$10<B1),$C$1:$C$10) Copy down the column change B10, and C10 to fit you needs best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rainxking" wrote in message ... Is it possible to have a running total go by date while not sorted by date? I would like to change the date but not change the sorting so that the running total of units is accurate at that period in time. SO basically I can see how many units I have at anygiven time. I hope this makes sense. I am sure someone has had to do this before... Below are two example tables. Example (sorted by Account): Acct---Date------units-runnning total A-----5/01/2006---10-----25 B-----4/01/2006---10-----0 C-----4/15/2006---15-----10 D-----5/15/2006---20-----35 Final Count----55 Example (sorted by Date): Acct---Date------units-runnning total A-----4/01/2006---10-----0 B-----4/15/2006---15-----10 C-----5/01/2006---10-----25 D-----5/15/2006---20-----35 Final Count----55 Thank you!!! -- rainxking ------------------------------------------------------------------------ rainxking's Profile: http://www.excelforum.com/member.php...fo&userid=9431 View this thread: http://www.excelforum.com/showthread...hreadid=542183 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
running total by date (not sorted by date though...)
I think I maynot have explained this clearly... Here us a better example what I have and what I am trying to do... I have units leaving and and coming in. I need a way to allocate. Going by the table below... I want to be able to change Joe's ShipDate to 6/25 and see how it affects the OnHand Units. This way I can look at everything, change a few ship dates and be able to accomidate more customers. Make Sense? Thanks again for all the help... If I can get this working it will save me hours and hours of work. Rep ShipDate Qty OnHand Joe 6/9/06 -1128 1200 Matt 6/9/06 -372 72 Jim 6/15/06 -150 -300 John 6/15/06 -6 -450 Sam 6/15/06 -12 -456 DELIVERY 6/15/06 4210 -468 Adam 6/25/06 -288 4678 Joe 6/25/06 -897 4390 --------------------------------- Total 6/25/06 3493 -- rainxking ------------------------------------------------------------------------ rainxking's Profile: http://www.excelforum.com/member.php...fo&userid=9431 View this thread: http://www.excelforum.com/showthread...hreadid=542183 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
running total by date (not sorted by date though...)
Is something I need to look into access to accomplish? -- rainxking ------------------------------------------------------------------------ rainxking's Profile: http://www.excelforum.com/member.php...fo&userid=9431 View this thread: http://www.excelforum.com/showthread...hreadid=542183 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Running total that stays with changes | Excel Worksheet Functions | |||
| Keeping formulas on running total and sorts | Excel Worksheet Functions | |||
| Pivot table for reporting sales performance | Excel Discussion (Misc queries) | |||
| RUNNING TOTAL | Excel Worksheet Functions | |||
| % of running total in pivot table | Excel Discussion (Misc queries) | |||
Hybrid Mode
