Need help in GOOGLE SHEETS (again)

Reply

  #1  
Old 04-06-16, 06:00 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
Need help in GOOGLE SHEETS (again)

In GOOGLE Sheets how can I show a running monthly total until the particular month ends. Then start a new running total for the new month. And so on and so on for the year? I need this to be as automatic as possible. Any help is appreciated.

Example:

Col A_______ Col B ___________Col C
Date ______# of units _____ running total
01-01 ________4_______________4
01-12 ________6 ______________10
01-25 ________3 ______________13 (this will be total for the month)
02-02 ________4 ______________4 (new running totals start on new month)
02-17 _______ 8 ______________12
02-28 ________1 ______________13 (this will be total for month)
03-05 ________3 ______________3 (new running totals start on new month)
 
Sponsored Links
  #2  
Old 04-06-16, 08:17 PM
Forum Topic Moderator
Join Date: Sep 2005
Location: USA
Posts: 4,687
Received 27 Votes on 26 Posts
The running total is pretty easy. It's harder to automatically restart for each new month.

In C3, the formula is =B3+C2 (last day's total + today)
Then just copy this formula down. It will make your running total work.

You have 3 choices I think for each new month:
1) Skip a row between months. Then the total in the row above will be zero (blank), so it will start new.

2) Change the formula in column C at the first day of each month to be =B4 (or whatever that row is)

3) Create a more complicated formula to figure out the date. Something like: (I haven't tested it, so consider it close but probably not quite)

In C3, =IF(DAY(A3) = 1, B3, C2 + B3)

Basically, if column A is the first day of the month, then just use the first day # of units.
If column A is not the first day of the month, add # of units to yesterday's running total.


Hope this helps!

-Mike
 
  #3  
Old 04-06-16, 09:01 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
It's harder to automatically restart for each new month.
Mike, Thanks for the quick reply.

Choice #1 seem like the easiest solution. But others will need to understand to skip a space at the start of new month. Ain't gonna happen.

Choice #2 is a bit more elegant but requires someone (me most likely) to do it. Again, if I don't, it won't.

Choice #3 will require some testing. I'll need to study it for a while. But it seems like an automatic method requiring little or no thinking. That's what I like. I Googled this problem and found one or two possible solutions but I had trouble following them. It may take me several weeks but I let you know if your suggestions work.

You see, right now I'm the only one using this spreadsheet (my choice and I demanded that option), but I can see where corporate may want to use it at other locations where I won't be able to control it. That's why I'm looking for an automatic solution. If I can make this work I'll protect all formula cells and only allow input cells to be accessed.
 
  #4  
Old 04-07-16, 09:58 AM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
I think #3 might have a problem in that in the example you show in post #1 there isnít necessarily a day 1 entry for each month. Looks like the formula would miss the transition from January to February in that example.

Could be wrong Iím only up to my 4th cup!!! lol
 
  #5  
Old 04-07-16, 11:38 AM
Forum Topic Moderator
Join Date: Sep 2005
Location: USA
Posts: 4,687
Received 27 Votes on 26 Posts
Very good point zoesdad, you are quite correct. The formula would need to compare the prior row's day to the current row's date to see if it's a new mont.

Something along the lines of:

=if( DAY(A3) < DAY(A2), B3, B3+C2 )
 
  #6  
Old 04-07-16, 02:49 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
Well I hate to be a nitpicker (I hope, lol) but I think that still won’t work. For example take the sequence:

1-Jan
2-Jan
12-Jan
15-Jan
17-Feb
19-Feb
28-Feb

I think the “Day” function only returns the day of the month. I could be wrong. Maybe there is a “Month” function.

I think I just found a MONTH function and this I believe works:

=IF( MONTH(A3) <> MONTH(A2), B3, B3+C2 )

but you need some valid base date in A2 to start off.
 

Last edited by zoesdad; 04-07-16 at 02:57 PM. Reason: added last formula
  #7  
Old 04-07-16, 08:15 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
Guys, You can't imagine how much this means to me. Thank you both for working on this.
Mike, your PM to me is great. I'll be looking at it in detail soon. And thanks to Zoesdad for helping out and pointing out Mike's initial flaws so he could correct them. I'll let you all know how it works out in the near future. But right now several other projects have taken front burner.
 
  #8  
Old 04-08-16, 11:27 AM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
Norm glad to be of any help at all.
 
  #9  
Old 04-08-16, 11:49 AM
pcboss's Avatar
Forum Topic Moderator
Join Date: Jul 2002
Location: Maryland
Posts: 14,941
Received 30 Votes on 26 Posts
A conditional expression could be used, something like If month = total.
 
  #10  
Old 04-08-16, 06:47 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
hi pcboss –

I may not really understand what you are saying but the statements
=IF(DAY(A3) = 1, B3, C2 + B3)
or

=IF( MONTH(A3) <> MONTH(A2), B3, B3+C2 )
are in fact conditional statements. This is the general form of the IF function in excel:






=IF (Logic_Test, Value_if_True, Value_if_False).
This statement IF(DAY(A3) = 1, B3, C2 + B3) in the cell C3 is equivalent to

IF (DAY(A3) = 1) THEN
...BEGIN
...C3 = B3
...END
ELSE
...BEGIN
...C3 = C2 + B2
...END
(dots above should be spaces)

or more specifically for example in the C Programming language:

If (Day(A3) == 1)
...C3= B3;
else
...C3 = C2 + B2;
(dots above should be spaces)

Or to be very accurate since IF is a function that takes 3 arguments and returns a value it can be written like this:

C3= IF(DAY(A3) = 1, B3, C2 + B3)
 

Last edited by zoesdad; 04-08-16 at 07:09 PM.
  #11  
Old 04-09-16, 05:56 AM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
Follow-up!

Works like a charm.

Here is just one area where I'm using it. I have several other applications also. Thanks again guys.

You'll need to magnify the image to see any detail, but the last column shows where the monthly totals are calculated.

 
  #12  
Old 04-09-16, 12:18 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
Thatís great Norm. IMHO that is a really good looking spread sheet. I use them for a few things but I can never make them look like that Ė lol.
 
  #13  
Old 04-09-16, 03:54 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
They usually start off very simple with just one word heading. Then when and if it seems to work and the work sheet is useful, I'll begin to jazz it up. Add color, add more info and the like. Although all my past managers have liked it, my current manager has taken a great interest in following my ability to keep the turn around time under seven days. He also wants to show corporate statistics and maybe incorporate our initial order write up with this spreadsheet.

I also have a very spiffy (have you heard that word lately?) auto mileage minder spreadsheet if anyone is interested in keeping detailed records of their gas fill ups and mileage. I've uploaded it several times to others when I belonged to one of the auto forums. It's an Excel format.

It tracks mileage, cost per mile, total miles, average cost per mile, average cost of gas per gal,
and percent of EPA for city,country and combined rating.

If anybody wants to take it and adjust the values for their particular car feel free. It's located at:

https://onedrive.live.com/?id=C4A587...A5870703C56D6F

You will only need to erase my data then unprotect the sheet and change cells J,K,and L 4 and list the EPA rating for your car. Then in J,K and L5 insert that same number in the formula and copy it down. Then be sure to protect the sheet again. Only those cells that require data input will be available to modify.
 
  #14  
Old 04-09-16, 06:13 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
Norm I downloaded it into Excel and saved it. Looks really good. I like the fact you even have the details for the gas stations. You are a stickler for details like me, lol. That Ford Focus gets some pretty good gas mileage.

I havenít been keeping track of my car, but I will now. I think thatís a good idea. I agree with all the data you keep and the column headings. Itís comprehensive.
 
  #15  
Old 04-10-16, 02:44 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
You will only need to erase my data then unprotect the sheet and change cells J,K,and L 4 and list the EPA rating for your car.
Did it. Couldnít find Unprotect at first since I donít know Excel very well, but I found it. That is a good option to utilize.
 
  #16  
Old 04-10-16, 04:11 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
Glad you found it. If you can think of any other things to add let me know.
 
  #17  
Old 04-10-16, 05:50 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
Sure thing!!........................................................................
 
  #18  
Old 04-13-16, 07:19 AM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
Quick follow up note.

With the new formula by Zorfdt to give me a running monthly total I can no longer do a sort (any sorting will cause the dates to go out of order). And that's OK. Just a small limitation but no a problem.
 
  #19  
Old 05-07-16, 12:11 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
Norm Ė

I donít know whether this would interest you but I started to play with google sheets to keep the old brain from dying Ė lol, and started to look into the running totals feature that you use. I found you can write java scripts for google sheets, but things are much different from Excel. There seem to be only 2 events which will automatically trigger a script: OnOpen(when a sheet is opened) and OnEdit(when a cell is edited). Excel has many events which you can hook your code to.

You can however take you scripts function name and place it in a cell with an ď=Ē sign like a formula and the script will be executed when that cell is selected. However, I could not get that to work. Whenever the script would execute I would get an error message saying the script did not have permissions to change a cell. I did everything possible to change the permissions for the entire sheet and the permissions stated ďEveryoneĒ could edit but ďwith a warning messageĒ. That did not seem to be the case, and in an obvious contradiction I could execute the same script from the script editor and the script completed and no such error message occurred. Something buggy there!

Also, and this seems to be a really bad bug; if I hooked the script to the OnEdit event, the script was started twice for one edit event. In other words, if I put the script inside the OnEdit function, and changed a cell, two instantiations of the script OnEdit function occurred running parallel to each other. That is really bad. As one is running down a column doing additions, instance 2 is running down the same column doing the same thing Ė with disastrous results. I couldnít believe it but I verified in many ways, that is exactly what was happening.

Anyway, the script does this:

(1) copies a Date column (identified by number) to a new column (identified by number) where the dates will wind up sorted oldest to newest

(2) copies a Quantity column(identified by number) to a new column (identified by number) where the quantities wind up added to form a running total by month

(3) the 2 new columns identified above in 1 and 2 must be adjacent to each other, with date column first.

(4) the 2 new columns are copied into an array for sorting.

(5) the new columns in the array are sorted by date in ascending order.

(6) the new quantity column in the array is adjusted to represent a running total by month.

(7) the array with the 2 columns now sorted and adjusted are stored back in the sheet in the 2 new columns.

So it doesnít matter what order the dates are in the original dates column, or whether there are blank rows, the date with its associated quantity will be sorted properly in the 2 new columns and the running totals made accordingly.

Maybe something along these lines would be helpful Ė or not,lol. The one thing basically wrong with this idea is that the script cannot be executed automatically based on sheet handling by the user. So there would never be a guarantee that the 2 new columns really represent reality. The user would have to make sure the script is executed after he makes changes to the sheet, i.e., after he adds a new entry or sorts rows, etc. I just donít see how it can be done the right way with the limited events google sheets gives you to work with and considering that the OnEdit event looks like it has a bad bug anyway. I wouldnít trust it.

Anyway, here is an example of the output:




Here is the code:



(second sheet here overlaps the first, line numbers indicate that)


 
  #20  
Old 05-07-16, 02:18 PM
Norm201's Avatar
Member
Thread Starter
Join Date: Sep 2013
Location: United States
Posts: 7,463
Received 72 Votes on 67 Posts
WOW! You sure are into it a lot deeper than I. Just a tad too complicated and like you said not quite there yet.

Scripts are a little tough for me to understand and as a result I shied away from them. But I do understand the logic and I do follow what you're doing.

I don't think I'm going to use the monthly total thing next year. This year was a way for my boss and I to compare what we thought was a one time phenomenon in terms of high quantity last year. This year we are running about 90% per month compared to last year. So I think the monthly totals are about average.

Again I want to tank you for your input.
 
  #21  
Old 05-07-16, 06:58 PM
Member
Join Date: Jan 2008
Location: Southeastern Pennsylvania
Posts: 2,947
Received 0 Votes on 0 Posts
Sure thing Norm. Iíd be glad to help anytime I can. Some of the stuff they do with scripts doesnít give me a warm fuzzy! One time I am absolutely positive they screwed up when copying columns into an array in the script. Their own debug software showed that.

They certainly seem to lose track and right hand left hand are out of sync in terms of permissions. You can execute the script from the script editor and change the sheet, but you canít have the script automatically executed from a cell change and change the sheet Ė because you donít have permissions. That makes absolutely no sense to me. So in this case, and I would bet many others, you canít even link a script execution to the userís sheet manipulation. You can only execute the script manually.

They only have two sheet events to trigger a script: cell edit (OnEdit) and open sheet (OnOpen). It looks like at least in some cases one cell change causes 2 OnEdit events and two instantiations of the OnEdit script to run in parallel. That is just a flat out bad bug. When you write OnEdit code you have to recognize that if your script takes a fairly long time to complete, another cell edit can occur and a second instance of your script may be started while the first is still running. But no way should you get two instantiations when editing one cell.

Also, you canít use the debugger when triggering your script from a sheet Ė for instance if you use the OnEdit event to trigger your script when someone edits a cell, your script will execute but not in debug mode. So if you put a breakpoint in your script to stop your script so you can look at variables to help find a bug, that breakpoint wonít happen if you change a sheet cell. The script will execute but not in debug mode and so the breakpoint is ignored.

To make it stop at the breakpoint you have to execute the script from the Debugger. But because your script is now running without having been actually called from a cell change, for which the script was written, the kind of variables which are passed to your script from and actual cell change arenít passed Ė so somehow you have to fake it in your script.

I was flabbergasted when I played with this stuff. Itís like google executives got their high school kids to do this Ė lol. I thought it might be something like Microsoft Excel Ė no way. This stuff is totally amateurish. Iím shocked.

But you can do some things with it. If you ever think of something Iíd be glad to help if I can.
 
Reply

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 Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread
Display Modes
 
Ask a Question
Question Title:
Description:
Your question will be posted in: