Cloud Computing -- Google Docs Way: November 2014

Saturday, November 29, 2014

yogi_Count The Number Of Invlalid Entries In A2 To A With Valid Entries Specified In C5 To C

Google Spreadsheet Post #1850

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-30-2014

post by Don Lawson:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dh7uB_anZsU
Count cells with invalid data
Is it possible to use a formula or script to count the number of cells in a given range that have data that violates the validation rules?
---------------------------------------------------------------------------------------------------------------------------------------

Thursday, November 27, 2014

yogi_Stats Such As LessonsTaken LastLessonDate PaymentMade BalanceDue Etc For Lessons taken By A Specified Student

Google Spreadsheet Post #1849

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-27-2014

post by EricF^:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/PYBlXgCvnO4
How do I collect only the data from range where a condition is true of the cell next door?

Hello amazing and helpful people of the internet. I run a language school and I'm taking December to fix our accounting nightmare. Thanks to the friendly people of this forum, I'm now able to pull the last date of a student's lesson to see whether their remaining balance is still valid (rather than checking manually when we remember to do it). This will allow me to be a decent human being and dutifully warn them before it expires too!

Here's how it works: The name of the student corresponds to the tab with their financials. I use a MAX(ARRAYFORMULA(Indirect())) formula to get the job done. Very clean.

Now I want to take this a step further. Next to the date of each lesson is a standardized teacher code which tells me who their lesson was with. This is important to pay our teachers (at the end of each month, we see who they taught and tally it up). I want to be able to see the last date that a student learned with a specific teacher. Even if the workaround is tough, I want to avoid scripts at all costs. Scripts are useful, but also buggy and I can't afford to have a formula that I can't personally understand and fix.

How do I collect only the data from range where a condition is true of the cell next door?

---

Oops, sorry Yogi. I did just that and forgot to include the link in the last reply.

https://docs.google.com/spreadsheets/d/1hRFmSX2lleluuobdw5u86vAmVFBbOUsD7cZOkZy1fbM/edit#gid=1050087198

-----------------------------------------------------------------------------------------------------------------------------

yogi_Set Up Formula To Sum Up Numbers From A Starting Date To A Specified Date Larger Than Starting Date

Google Spreadsheet Post #1848

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-27-2014

post by Antony Trimikliniotis:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/zZalvF63xPk
how to sum a range using OFFSET

Below is a formula that works well in excel but returns a parse error on google sheets.

=IFERROR(SUM(E11:OFFSET(E11,0,MATCH($B$5,$E$4:$P$4)-1,1,1)),0)

the data looks like this with the YTD being column B and Jul-15 column P. The aim of the formula is to try and sum from aug to oct(E:G) and then next month aug to nov(E:H) etc

YTDFY 2014-15FY 2014-15Aug-14Sep-14Oct-14Nov-14Dec-14Jan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15

Oct-14No.No.No.No.No.No.No.No.No.No.No.No.No.

2.02.002.02.02.02.02.02.02.02.02.02.02.02.0

£'000£'000£'000£'000£'000£'000£'000£'000£'000£'000£'000£'000£'000£'000

#ERROR!6868666666666666

---

i have attached the file

the result in column B ,rather than returning an error, should sum columns e:G whenever I update the date in cell b5

The content of this email (and any attachment) is confidential. It may also be legally privileged or otherwise protected from disclosure.

This email should not be used by anyone who is not an original intended recipient, nor may it be copied or disclosed to anyone who is not an original intended recipient.

If you have received this email by mistake please notify us by emailing the sender, and then delete the email and any copies from your system.

All views and opinions expressed in this electronic message and its attachments are those of the sender and do not necessarily reflect the views and opinions of SAM Learning Ltd.

SAM Learning Ltd Registered in England No 2826785 Registered Office Webber House, 26-28 Market Street, Altrincham, Cheshire WA14 1PF

Attachments (1)

Copy of ACT - SAM L 2014-10.gsheet

221 B View Download

-----------------------------------------

Wednesday, November 26, 2014

yogi_Pull The Last Entry In A Column In Another Sheet And Deduct From it The Penultimate Entry In The same Column -- Formula Should Work In Google NewSheet As Well As In EXCEL

Google Spreadsheet Post #1847

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-26-2014

post by Clayton hazell:
https://productforums.google.com/forum/#!mydiscussions/docs/0t4hM5hD4W4
VLOOKUP help next to last row in column
need help please. I have a workbook with 2 pages. I input data on the 2nd page downward in column D and have a vlookup command "=VLOOKUP(9.99999999E+307, 'SVY''S'!D$1:D$65536,1)" that finds the last entered value in that column and enters it on page one. I'm trying to change the formula to subtract the last two values in that column from page 2. Keep in mind I add rows all the time and need it to update as I add rows to it. Any ideas? Thank you in advance
---

Ok so I made a new sheet here's the link.

https://docs.google.com/spreadsheets/d/1W38eOhNuLoWNCZamyvTgkvdYQomhQJASU6_jGem21fI/edit?usp=sharing

a) I input data on the 2nd page downward in column D and have a vlookup command "=VLOOKUP(9.99999999E+307, 'SVY''S'!D$1:D$65536,1)" that finds the last entered value in that column and enters it on page one. I'm trying to change the formula to subtract the last two values in that column from page 2. Keep in mind I add rows all the time and need it to update as I add rows to it.

So with these numbers, if the formula is correct then I should get a value of 0.68(1.67-0.99) or (SVY'S Page D23-D22) in the cell on the horizontal page in cell B3. The major issue is the formula that I have only finds the last row of column D with an entered value and places the value for me. I'm trying to find a way to get it to subtract the last two rows of column D as I update the sheet. Does this help?
---
Sorry i'm using excel I thought i would be the same.
-----------------------------------------------------------------------------------------------------------------------------

following is a non-ArrayFormula solution that works both in Google NewSheet as well as in Excel

yogi_Pull The Last Entry In A Column In Another Sheet And Deduct From it The Penultimate Entry In The same Column

Google Spreadsheet Post #1847

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-26-2014

Ok so I made a new sheet here's the link.

https://docs.google.com/spreadsheets/d/1W38eOhNuLoWNCZamyvTgkvdYQomhQJASU6_jGem21fI/edit?usp=sharing

----------------------------------------------------------------------------------------------------------------------------------------------------

Tuesday, November 25, 2014

yogi_Conditionally Format Column M If Columns D G And H (all 3) House Y In The Corresponding Row

Google Spreadsheet Post #1846

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-25-2014

post by Enrique Boil:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KnDT04pZ6kY
Change color of a cell depending on the content of three different cells

Change color of a cell depending on the content of three different cells, When all the three cells contains "Y" cell M become green. Many thanks.

https://docs.google.com/a/jaguarlandrover.com/spreadsheets/d/1rcN5nGk-k1FwTPECS_YK3261xwChVU0nceifiWbELic/edit?usp=sharing

---------------------------------------------------------------------------------------------------------------------------

yogi_Compute Row By Row Balance For A Check Register Type SetUp With Starting Balance

Google Spreadsheet Post #1845

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-25-2014

post by Richard Puerto:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KnDT04pZ6kY
Needing help with an ArrayFormula, please.

I created a check register spreadsheet and would like to use an array formula to calculate the balance in a more efficient manner as opposed to a formula for each cell. The problem is that I have to subtract from the cell above in the prior row. This is the formula:

=ARRAYFORMULA(If(AND(ISBLANK(D3:D1000),ISBLANK(E3:E1000)),"",F2 - D3:D1000 + E3:E1000))

I can't figure out how to subtract from the cell/row (F2-999) in the row above while using the proper syntax for the array formula. It is also calculating a balance when cells in D and E are blank, but it shouldn't.

I have shared the sheet :

https://docs.google.com/spreadsheets/d/14I_yoj1Dr3WQC-SfXyKg9gqM0A4-cnU45x6gcAeXetI/edit?usp=sharing

Thanks for any possible help.

--------------------------------------------------------------------------------------------------

Monday, November 24, 2014

yogi_WorkAround For Computing ElapsedTime In Days Hours and Minutes From Specified StartTime

Google Spreadsheet Post #1844

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov-24-2014

post by Matt Speth:
https://productforums.google.com/forum/#!msg/docs/JOLE6EQynW0/-8S0vR-5jJoJ
Formatting Help with Day and Time Durations

Hello.

I have a tally sheet and a chart that calc durations for some running races. I couldn't get the information to format into a D HH:MM so I played with it in excel and then re-sent it to Sheets.

Sheets doesn't have (apparently) the format I'm looking for. This is the Excel information descending a column...

0 d 01:41

0 d 02:45

0 d 04:25

0 d 13:08

0 d 14:20

0 d 16:26

0 d 21:03

1 d 03:47

1 d 07:45

And this is the Sheets interpretation (WRONG):

30 d 01:41

30 d 02:45

30 d 04:25

30 d 13:08

30 d 14:20

30 d 16:26

30 d 21:03

31 d 03:47

31 d 07:45

Again, both items are formatted to - D "d" HH:MM - but Sheets comes up with Calendar Days. Any suggestions to get a duration?

Another issue is that I need all of the information - it can't be a text string because I need to chart the information once it's been collected.

--------------------------------------------------------------------------------
Let us first look at getting the Days Hours:Minutes right ... and then we can look at getting these as Numbers for plotting purposes

Cloud Computing -- Google Docs Way