PC Review


Reply
Thread Tools Rate Thread

Another Running Totals Problem

 
 
F J
Guest
Posts: n/a
 
      18th Sep 2011
Hi, I have a several large spreadsheets which have data that is broken
into groups. Each of these groups needs a running total within its
own group. I had some similar spreadsheets the other day and someone
here gave me a great formula that worked on those using the data in
column A as the criteria for a SUMIF formula:

=IF(A1="","",SUMIF($A$1:A1,A1,$B$1:B1))

The problem with these new files is that unlike the other ones, there
is nothing that is always unique about the data in each group that
could be used for a SUMIF formula or something like that. The only
indication that the data should even be in separate groups is that
each group is separated by a blank row. The data is set up like
this. The running totals formula would appear in column C:

Column A Column B Column C
Apples 100 100
Apples 200 300
Apples 500 800

Apples 300 300
Apples 100 400

Oranges 200 200
Oranges 300 500

Oranges 500 500

Pears 300 300
Pears 200 500

Apples 200 200
Apples 500 700

Is there any way to create a formula that can just be copied all the
way down the column instead of having to go through and change it for
each group? Thank you in advance for any help or information.
 
Reply With Quote
 
 
 
 
strawberry
Guest
Posts: n/a
 
      19th Sep 2011
So if cell A in the preceding row is blank, Cn=B, otherwise Cn=B+(Cn-1)

I'm far from expert but that seems pretty easy to me!
 
Reply With Quote
 
Rob P
Guest
Posts: n/a
 
      23rd Sep 2011
this will work from row 2

=IF(A2="","",IF(A1=A2,C1+B2,B2))
 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      23rd Sep 2011
F J wrote on 9/18/2011 :
> Hi, I have a several large spreadsheets which have data that is broken
> into groups. Each of these groups needs a running total within its
> own group. I had some similar spreadsheets the other day and someone
> here gave me a great formula that worked on those using the data in
> column A as the criteria for a SUMIF formula:
>
> =IF(A1="","",SUMIF($A$1:A1,A1,$B$1:B1))
>
> The problem with these new files is that unlike the other ones, there
> is nothing that is always unique about the data in each group that
> could be used for a SUMIF formula or something like that. The only
> indication that the data should even be in separate groups is that
> each group is separated by a blank row. The data is set up like
> this. The running totals formula would appear in column C:
>
> Column A Column B Column C
> Apples 100 100
> Apples 200 300
> Apples 500 800
>
> Apples 300 300
> Apples 100 400
>
> Oranges 200 200
> Oranges 300 500
>
> Oranges 500 500
>
> Pears 300 300
> Pears 200 500
>
> Apples 200 200
> Apples 500 700
>
> Is there any way to create a formula that can just be copied all the
> way down the column instead of having to go through and change it for
> each group? Thank you in advance for any help or information.


Starting in C2:

=IF(A2="","",IF(C1="",B2,C1+B2))

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      24th Sep 2011
> Starting in C2:
>
> =IF(A2="","",IF(C1="",B2,C1+B2))


If you need to start in row 1 then you can create a local defined name
as follows:

Select A2
In the DefineName dialog:
In the NameBox type LastCell
In the RefersTo box type =A1
Click the 'Add' button and close the dialog

In the formula above, replace C1 with LastCell

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      24th Sep 2011
Justto be clear, the new formula in C1 is...

=IF(A1="","",IF(LastCell="",B1,LastCell+B1))

Just copy down however many rows!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


 
Reply With Quote
 
F J
Guest
Posts: n/a
 
      25th Sep 2011
Hi, Rob, thanks for your response. Your formula works great.


On Sep 23, 2:55*pm, Rob P <r.p.philli...@gmail.com> wrote:
> this will work from row 2
>
> =IF(A2="","",IF(A1=A2,C1+B2,B2))

 
Reply With Quote
 
F J
Guest
Posts: n/a
 
      25th Sep 2011
Hi, GS, thanks for your replies. I tried both of your formulas and
they work great.



On Sep 23, 7:04*pm, GS <g...@somewhere.net> wrote:
> Justto be clear, the new formula in C1 is...
>
> * =IF(A1="","",IF(LastCell="",B1,LastCell+B1))
>
> Just copy down however many rows!
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc


 
Reply With Quote
 
F J
Guest
Posts: n/a
 
      25th Sep 2011
Hi, strawberry, thanks for your reply. I wasn't sure exactly how to
apply your formula, but I will look into it.


On Sep 19, 11:38*am, strawberry <zac.ca...@gmail.com> wrote:
> So if cell A in the preceding row is blank, Cn=B, otherwise Cn=B+(Cn-1)
>
> I'm far from expert but that seems pretty easy to me!


 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      26th Sep 2011
F J wrote on 9/25/2011 :
> Hi, GS, thanks for your replies. I tried both of your formulas and
> they work great.


Glad to help. Thanks for the feedback!
Best wishes...
>
>
>
> On Sep 23, 7:04*pm, GS <g...@somewhere.net> wrote:
>> Justto be clear, the new formula in C1 is...
>>
>> * =IF(A1="","",IF(LastCell="",B1,LastCell+B1))
>>
>> Just copy down however many rows!
>>
>> --
>> Garry
>>
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc


--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Running Totals Problem F J Microsoft Excel Discussion 2 16th Sep 2011 11:42 AM
Running totals in gridview (when you have many totals required) =?Utf-8?B?Um9iZXJ0IENoYXBtYW4=?= Microsoft ASP .NET 3 16th Feb 2007 12:03 PM
simple problem, i think... with running totals chris_ Microsoft Excel Discussion 1 7th Jul 2005 11:33 PM
Running Totals Problem Dale Clarke Microsoft Access Form Coding 1 28th Aug 2003 04:18 AM
Running Totals Problem Dale Clarke Microsoft Access Form Coding 0 13th Aug 2003 04:15 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:29 AM.