2

Being a (productive) geek, I want to keep track of the trend of my flextime. I keep a personal document of my hours worked in Google Sheets, having one column for hours worked every day. By subtracting 8 from this number I calculate my +/- flex earned that day.

First, I wish to display the cumulative sum for the total flextime account, corresponding to every working day, i.e.:

  A      B      C
1 Time   Flex   Cumulative Flex
2 8.25   0.25   0.25
3 8.5    0.5    0.75
4 7.75  -0.25   0.5

Second, I want to plot it, having some kind of date reference on the horizontal axis, e.g. day of the month.

I tried making an ArrayFormula as e.g. C3 =ArrayFormula(B3:B100-8+C2:C99) but saw:

error: Circular dependency detected

1

Adding a column for date and calculating the C values with:

=if(istext(D1),C2,D1+C2)  

in D2 copied down a chart might be plotted of this Data Sheet1!A1:A4, Sheet1!D1:D4 with Use row 1 as headers:

WA17439 example

-2

Removed previous things as it was going to be a bit messy

So firstly I ditched you Cumulative Flex column.

I then added the panorama pivot table gadget to the sheet and selected the data

The I created a formula using the running sum and put in flex time the result of which can be seen enter image description here here

And a tutorial for the gadget can be found here

  • But wont that prohibit the cumulative behaviour? I mean C4 would depend on C3. – Theodor Jul 20 '11 at 15:34
  • Yes, the issue is though, that I would prefer to use an arrayFormula to automatize the operation... Otherwise I'll have to copy/paste and increse the numbers by one... Hmm. – Theodor Jul 21 '11 at 8:39
  • @Theodor see my new edit this one is a really nice solution :) – Matt Jul 21 '11 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.