UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Trying To Find A Formula, Office 2013    
 
   
ADezii
post Apr 5 2020, 06:28 AM
Post#1



Posts: 2,989
Joined: 4-February 07
From: USA, Florida, Delray Beach


I have a small Project that is absolutely driving me crazy and I hope that one of you guys can help me out. Given the Excel Table depicted below, how can I generate (for each Row) a Running Total in the Remaining Home Column for each individual? Each person is allowed to work at home for a maximum of 80 hours regardless of time frame and I need to maintain a Running Sum for this. For instance: Fred worked 8 hours from home during the week of 4/5 to 4/11 leaving him a Remaining Balance of 72 hours, he then worked 20 hours at home from 4/12 to 4/18 for a Remaining Balance of 52 hours ( 80 - (8 + 20)), last but not least he worked 15 hours at home from 4/19 to 4/25 for a Remaining Balance of 37 hours ( 80 - (8 + 20 + 15)). I can easily accomplish this with a UDF, but I would prefer a Formula if possible that I can Copy-N-Paste to all Rows. Thanks in advance.
This post has been edited by ADezii: Apr 5 2020, 06:28 AM
Attached File(s)
Attached File  Running_Total.JPG ( 73.09K )Number of downloads: 3
 
Go to the top of the page
 
arnelgp
post Apr 5 2020, 07:53 AM
Post#2



Posts: 1,528
Joined: 2-April 09
From: somewhere out there...


for you expert.
try using SumIfs().
let suppose the data starts at
Column A:, below "Remaining Home", put
the formula:

=80-(SUMIFS(E:E,A:A,A2,C:C,"<="&C2))




--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
ADezii
post Apr 5 2020, 01:38 PM
Post#3



Posts: 2,989
Joined: 4-February 07
From: USA, Florida, Delray Beach


@arnelgp:
Looks like your Formula will work like a charm, thank you! thanks.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th May 2020 - 11:15 AM