• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running total by person

D

DashboardNovice

Member
In the attached file, I have a formula to compute a running total in column H. However I want to do this by person. When the formula gets to the yellow row, the results are inaccurate because that row has a new person.

Is there a way to do this w/o a pivot table or w/o inserting subtotals?

Is there a way to do this by a formula?
 

Attachments

  • Running Total by Person.xlsx
    9.6 KB · Views: 2
DashboardNovice said:
In the attached file, I have a formula to compute a running total in column H. However I want to do this by person. When the formula gets to the yellow row, the results are inaccurate because that row has a new person.

Is there a way to do this w/o a pivot table or w/o inserting subtotals?

Is there a way to do this by a formula?
Click to expand...



I just found the solution. Just use SUMIFS and don't lock the 2nd cell reference in the ranges.
 

Attachments

  • Running Total by Person.xlsx
    10.1 KB · Views: 3
Because your names occur in blocks, it is also possible to accumulate the result with a formula of the sort
=IF( prior Name = current Name, prior total ) + (current Value)
where 'current' is a relative reference to the formula row and 'prior' is another relative reference to the row above. The reference 'prior Name' is the intersection of the row 'prior' and column 'Name'.

Note: As it stands, the column names must include the header row it the first data row is to evaluate correctly.
 

Attachments

  • Running Total by Person (PB).xlsx
    10.3 KB · Views: 11
Peter Bartholomew said:
Because your names occur in blocks, it is also possible to accumulate the result with a formula of the sort
=IF( prior Name = current Name, prior total ) + (current Value)
where 'current' is a relative reference to the formula row and 'prior' is another relative reference to the row above. The reference 'prior Name' is the intersection of the row 'prior' and column 'Name'.

Note: As it stands, the column names must include the header row it the first data row is to evaluate correctly.
Click to expand...

Wow this is a very interesting solution. I don't think I have seen this method before. I can see that you are using named ranges.

Your formula has "current Value" but the named ranges has "current." Similarly, our formula has "current Name" but the named ranges have "this Name."

What is the difference between these? Why aren't you using the named range in your formula (and instead using a similar name, like an alias or something)?

How did you define your named ranges?

Can you refer me to any links that explain what you are doing?

Thank you for suggesting this solution.
 
Your formula has "current Value" but the named ranges has "current."
Click to expand...
Hi
The 'normal' spreadsheet development process involves the user finding the value they wish to use and clicking on it to enter a direct reference into their formula. What I have done here is to take a step back and try to capture the logic that leads to the user selection.

If you start to edit any instance of my formula you will see Excel colour entire rows and columns of the table. The columns I have called 'Total' and 'Value' to correspond to the table headings, whilst the rows are 'current' and 'prior'. I might define the last names first as absolute row references but then I go into name manager to remove the '$'s that make the row absolute (the F4 key is also a way of achieving this).

The key to the working of the formulas are the operators (colon, space and comma) that may be used to define new ranges from range operands. These operators give 1) the smallest range that envelopes the constituent ranges; 2) the intersection of the two ranges; 3) the union of the two ranges.

Thus (prior name), besides reading like natural language, actually states that the required text is to be found where the 'prior' row intersects the 'name' column.

I realise that this must look very strange but what it sets out to do is capture the developer's intent as well as the answer.
 
So does it stop there? Not really …

If you wanted to calculate a specific running total without needing the previous values you might
1) Find the index of the first row that corresponds to the required person
= MATCH( current Name, Name, 0 )
2) Return a reference to the corresponding 'Value' cell
= INDEX( Value, MATCH( current Name, Name, 0 ) )
3) Define a range that starts with the first value and ends with the current value
= INDEX( Value, MATCH( current Name, Name, 0 ) ) : (current Value)
4) Sum the range to give a value for the running total
= SUM( INDEX( Value, MATCH( current Name, Name, 0 ) ) : (current Value) )

The fascinating thing is that Excel will, in fact, support such radically different thought processes from the default 'click and pray'.

And all that is before one allows array formulas into the mix!
 
Peter Bartholomew said:
Hi
The 'normal' spreadsheet development process involves the user finding the value they wish to use and clicking on it to enter a direct reference into their formula. What I have done here is to take a step back and try to capture the logic that leads to the user selection.

If you start to edit any instance of my formula you will see Excel colour entire rows and columns of the table. The columns I have called 'Total' and 'Value' to correspond to the table headings, whilst the rows are 'current' and 'prior'. I might define the last names first as absolute row references but then I go into name manager to remove the '$'s that make the row absolute (the F4 key is also a way of achieving this).

The key to the working of the formulas are the operators (colon, space and comma) that may be used to define new ranges from range operands. These operators give 1) the smallest range that envelopes the constituent ranges; 2) the intersection of the two ranges; 3) the union of the two ranges.

Thus (prior name), besides reading like natural language, actually states that the required text is to be found where the 'prior' row intersects the 'name' column.

I realise that this must look very strange but what it sets out to do is capture the developer's intent as well as the answer.
Click to expand...

I'm not used to seeing this type of syntax. Do you have any sources where I can read more about this? Or maybe can you suggest keywords I can search on?
 
You must log in or register to reply here.
Back
Top