yogi_Count Submissions By Year And Month In Another Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #651   Jul 24, 2012     www.energyefficientbuild.com.


user Nieboy1 said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/4cRXTQh4ml8)
simple count submittions query 
hi, I am looking to simply return the number of submitions for each month on a form spreadsheet ( https://docs.google.com/spreadsheet/ccc?key=0AuXtZZ2omsU_dDhXQ1VjallEVHE3YjFrQVhpT3VDc3c)

similar to the format that A11 in "Derivatives" on this workbook ( https://docs.google.com/spreadsheet/ccc?key=0AuXtZZ2omsU_dFJhVFdfM0dSR2ZfTklKakFnVW5WSVE )

I am hoping that it is a simple modification to that formula to accomplish this.

Thanks 

Ryan

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

following is a solution to the problem

yogi_Setup A Sheet To View Project Status in Real Time

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #650   Jul 23, 2012     www.energyefficientbuild.com.


user tomgreeen said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Zn3CygQg9P4)
Using Googleclock() and Vlookup together
Hello

I'm trying to combine Googleclock() and Vlookup (or INDEX) to monitor a project in real-time by comparing the percent completed against how much should have been completed at this given point. I've done what I think should work but it doesn't yet, I was wondering if you have any ideas?

I've created a column with the date and time counting backwards by 1 minute at a time - as this is the unit that GoogleClock counts up with. (Incidentally if I could get GoogleClock to just show the time and not the date, that would simplify things a lot). Eg

TIME

23/07/2012 15:00:00

23/07/2012 14:59:00

23/07/2012 14:58:00

23/07/2012 14:57:00

23/07/2012 14:56:00

23/07/2012 14:55:00

In the next column I have the target percentages for each of those given times - calculated by taking the number of minutes in the entire project time (about 9 hours in this case) and dividing by 100:

% PROOF AIM

100%

100%

100%

99%

99%

99%

99%

99%

99%

98%

98%

I have other columns for other metrics I measure during the project but have left these out until I get this to work.

I want Vlookup to tell me what % we should be at the current time. This will then power a dynamic chart which shows whether we are ahead of or behind schedule in the various metrics.

So I have tried the formula vlookup(GoogleClock(),A:E,2)

This only seems to be returning the % associated with the bottom time on the list, rather than the time which matches the GoogleClock() time

I've also tried putting GoogleClock() in another cell and pointing the Lookup function at that, and also changing the formatting of the time column and the cell with the current time to Normal and Decimal formating but this doesn't seem to work either. It's weird as the numbers look exactly the same, but the function doesn't seem to be able to read them properly.

I've also tried it with the INDEX function:

=index(googleclock(),A:E,2)

...but I get the message Error: not a number: TIME with this formula. Though I may be doing something wrong as I don't know this function very well (not that I know Vlookup well either!)

Any ideas about what I'm doing wrong? Is there a way to turn the time-based columns into pure numbers so that these work?
Thanks
Tom

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

following is a solution to the problem

yogi_Set Up A Calendar For Specified Number of Weeks Starting With A Specified Date

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #649   Jul 23, 2012     www.energyefficientbuild.com.


user Lachimus said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/qPldtxQUjO0)
How do I make a list of weeks horizontally?

I am trying to make a spreadsheet for my semester at Uni and I'd like to make a list of weeks from Week 1 - 15 across the top, and the subsequent days Monday - Sunday down the left and then have the dates of each cell come up as well as have other data in the cells pertinent to the my course and the dates.

Is that clear enough? Is that possible?

thanks :)

Lachie :D

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

following is a solution to the problem

yogi_Compute Elapsed Time Row By Row For Given From And To Times In Hours Minutes And AM_PM

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #648   Jul 22, 2012     www.energyefficientbuild.com.


user wal1006 said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/_is7D5_F5Eg)
Issue converting time from 12 hour into 24 hour format 
I am working on a spreadsheet to process form data.

The form asks users to enter a start time and an end time. In order to make sure that the users are not confused by the 24 hour format, my form asks for Start Time Hour (1-12), Start Time Minutes (15-minute increments), and AM/PM. The same for End Time.

In order to calculate the time difference, I first convert both times to 24 hour format. This is where I have the problem, because when I convert 12:00 pm to 24 hour format, the result is 24:00:00, and 12:00 am converts to 12:00:00:

A1: Start Time Hour

A2: 10

B1: Start Time Minutes

B2: 0

C1: am/pm

C2: am

D1: Start Time 24hr

D2: 10:00:00 using =ARRAYFORMULA((TIME(A2:A,B2:B,00))+((C2:C)="pm")/2)

E1: End Time Hour

E2: 12

F1: End Time Minutes

F2: 0

G1: am/pm

G2: pm

H1: End Time 24hr

H2: 24:00:00 using =ARRAYFORMULA((TIME(E2:E,F2:F,00))+((G2:G)="pm")/2)

I1: Total Time

I2: 14.00 using =ARRAYFORMULA((H2:H-D2:D)*24)

What am I doing wrong in H2?

Thanks for your help!

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

following is a solution to the problem

yogi_Compute Running Total By Row For Hours Worked At Rates Embedded In Row1

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #647   Jul 22, 2012     www.energyefficientbuild.com.


user trueblueroses said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/VXIMYSIxLy0)
HOW DO I MAKE A RUNNING TOTAL OF MY HOURS AND COMPUTE
HOW DO I MAKE A RUNNING TOTAL OF MY HOURS AND COMPUTE PAY RATE MY FIRST COLUMN ARE DAYS, SECOND COLUMN HOURS WORKED AT 12.,50 RATE OF PAY, THIRD COLUMN HOURS AT 15.00 RATE FOURTH COLUMN AT 20.00RATE OF PAYTHE BOTTOM TWO ROWS ACROSS THE COLUMNS ARE TO
------------------------------------------------------------------------------------------------
following is a solution to the problem:

yogi_Summarize Columnar Data And Present It In Pivot Table Like Format

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #646   Jul 22, 2012     www.energyefficientbuild.com.


user jaegeetee said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/kkhvRHP2K9c)
How would I count if conditions are met in the same row but different column?
I have a dataset that counts what was ordered at a coffee shop by men vs women.  How would I count how many times a man ordered a latte
----
it would be something like this (obviously its in cells in a google spreadsheet):

Gender          Drink  

Male            latte

Female          Coffee

Male            Frappuccino

Female          Latte

Male            Latte

Male            Coffee

I was wanting to count the amount of Males that ordered the various drinks.  So my goal was to have another section on the spread sheet would look like this:

                 Male           Female

Coffee            1               1

latte             2               1

frappuccino       1               0

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

following is one solution to the problem

yogi_Compute Row By Row And Running Totals With A Given Starting Balance And Some Multipliers Embedded In Questions

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #645   Jul 22, 2012     www.energyefficientbuild.com.

UPDATED JUN-05-2016

user eldunari said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Ca6E7gb2VN4)
Have spreadsheet form perform calculations as users submit forms

I am creating a form for users to submit production records from a lunchroom.  They must supply the number of meals served for Paid, Free, and Reduced lunches.  Then that number needs to be multiplied by a fixed number that I don't want the user to have to enter.  Is there a way to just ask the user for the number of meals served and submit the form and then have the spreadsheet calculate the total $ for the meals served?

Thanks.

Caron.

----

Sorry for the lack of information.  I've added some rows to the spreadsheet.  However, the formula that was added in G isn't calculating correctly.

G should be B + C + D + E + F (D, E, and F should be user input * price in header)

Does this clarify?  Please let me know if further info is needed.  And I do appreciate your assistance.  This has escaped me for two months now and I'm at the point of giving up.....but I hate giving up... :)

Thanks again.

Caron.

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

following is a solution to the problem