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.
-----------------------------------------------------------------------------------------------------------------------------


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.


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
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.


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? 
----------------------------------------------------------------------------------------------------------------------------------------------------



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.
---------------------------------------------------------------------------------------------------------------------------


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 :


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 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 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