Cloud Computing -- Google Docs Way

Monday, October 7, 2013

yogi_Sum Up Total Sales By Speaker For Specified Quarter (or for all quarters)

Google Spreadsheet Post #1386
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Oct 07, 2013
user: Rosa Crisostomo (http://productforums.google.com/forum/?zx=6ycrwucpw4f9#!category-topic/docs/spreadsheets/vMMstzoCdEY)
Return a count of records between specified dates for each speaker
On worksheet 'Speaker Totals' I need to calculate how many orders per quarter (first example: July - Sept) each speaker sold. All this data is listed on worksheet 'Event Data'.

Column A = Dates
Column C = Speaker
Column H = Total Orders

I would normally use sumifs but google docs does not allow this function.

I am using Windows 7
--------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Count Number Of Machines Older Than Specified Number Of Years

Google Spreadsheet Post #1385
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Oct 07, 2013
user: Chad S. (http://productforums.google.com/forum/?zx=6ycrwucpw4f9#!category-topic/docs/spreadsheets/3YaeC-rDR54)
'Google Spreadsheet, Query Date older than 5 years ago

I am trying to query a count of machines that are older than 5 years.

My machine data is in one tab with date time stamps in column K like "8/8/2008 8:08:08"

I have tried many different variations to get the query right... but my latest is like this

=query(RawDataMachines!B:K,"select count(K) WHERE K + INTERVAL 5 YEAR < NOW() label count(A1''")

Been trying to solve this issue for over a week now.

Any help would be appreciated.

Thanks in advance.

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

yogi_Count Number Of Teachers By Age Group From Table With Age in Row 1 And Teacher names In Row 2

Google Spreadsheet Post #1384
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Oct 07, 2013
user: Oxbridge Mentors (http://productforums.google.com/forum/?zx=6ycrwucpw4f9#!category-topic/docs/spreadsheets/cgSX5cAu-I4)
Count the number of names within a cell
Hi there,

I have a spreadsheet where I am keeping different numbers of names within different cells.

Eg -
Cell C1 contents: Jack Smith, Joan Jacob, Paul Thomson.

What I would like is another cell that says how many names C1 has (in this case, 3).

For context, I have to have the names in a cell because I am creating a spreadsheet for how many teachers I have who teach certain subjects and at certain ages. For the column headers are the age ranges (Primary, Secondary, GCSE, degree level etc) and the row names are the subject areas (Maths, English, Sciences). Hence a particular cell tells me the whole list of teacher who can teach, say, Maths at degree level. I need a count of the names who can teach X subject at Y age in order to know whether I need to hire more teachers.

Hope someone can help! Hope I've explained well enough.

In the attached document mock up, it is the red row that I would want to have cells saying how many teachers I have for each subject+age.

Best,

James
--------------------------------------------------------------------------------------------------------------------------

Sunday, October 6, 2013

yogi_Compute Sum Of Specified Number Of Entities In Column A

Google Spreadsheet Post #1383
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Sep 28, 2013
user: David Courtney

Friday, October 4, 2013

yogi_Compute MIN And MAX Of LOWs And HIGHs For A Stock Within A Specified Range of from And To Dates

Google Spreadsheet Post #1382
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Sep 28, 2013

user Grove Under (http://productforums.google.com/forum/?zx=kv2v4ugrv7vv#!category-topic/docs/spreadsheets/VHavMFBwfVg)
=MAX function bug when calling GoogeFinance?
I tried to find out the lowest low of a stock price since 9/1/2013 via googlefinance using the formula below and it worked fine:

=MIN(googlefinance("GOOG","low", "9/1/2013", today(), 1)) <- This formula works fine and returns the proper answer
However, when I tried to find the highest low (or the highest high) using the following formulas, I could not get it to work:

=MAX(googlefinance("GOOG","low", "9/1/2013", today(), 1))
=MAX(googlefinance("GOOG","high", "9/1/2013", today(), 1))

The =MAX function returns a value of 41550.666667, no matter what stock symbol is used.

Is this a bug, or is the =MAX function not being used properly with googlefinance? Thanks!
-------------------------------------------------------------------------------------------------------------------------------------------------

there is no bug -- you have to operate on the second column values of the output from GoogleFinance function --
have a look at the illustration I present in the following

yogi_WorkAround For Conditionally Formatting (Flagging) Every Third Instance Of A Student's Name For Disciplining

Google Spreadsheet Post #1381
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Sep 28, 2013

user spencerb (http://productforums.google.com/forum/?zx=kv2v4ugrv7vv#!category-topic/docs/spreadsheets/QZg_pnsCJ1o)
How to Apply Background Color to every 3rd instance of a name
I'm creating a "discipline form" of sorts, and am wanting to come up with a formula that would apply a red background color with white text to a row of of cells (probably only 4 cells across) for every third instance of the name of a student.

Here's a copy of the form - https://docs.google.com/spreadsheet/ccc?key=0Ap6iyNpVnim9dDVFTVdfUms3cThMT2Y0SENidjdwMHc&usp=sharing

I've highlighted the third instance of a student, just to show you what the highlighting of the row should look like. And ideally, the formula would allow me to choose a hex color to color the row whatever color it needs to be.

And if a student's name is provided 6 times, it would have 2 rows highlighted red. 9 times, it would have 3 rows highlighted red, and so on...

Thanks for your help!

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

yogi_WorkAround For Showing Data in Reverse Order (Last Eneterd First Shown ... like LIFO ... Last In First Out)

Google Spreadsheet Post #1380
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Sep 28, 2013

user Olga Godoy Giménez (http://productforums.google.com/forum/?zx=b71iiwt6rygl#!category-topic/docs/spreadsheets/CyExgxgRXLc)
How can I get the data to appear in reverse order?
'd like the data to appear in reverse order so that new data will appear in the upper rows while old date will move down as new data is entered.
Can this be done? Thanks in advance!
----------------------------------------------------------------------------------------------------------------------------------------------------------------
in the solution presented here,
1) I first created a virtual seconfd column to show corresponding row number of entries in column A
2) then I sorted the entry in column A by row numbers in the virtual column by descending oder