Monday, June 26, 2017

yogi_Count responses per hour in google forms spreadsheet

Google Spreadsheet   Post  #2194
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-26-2017
question by zeke29:
https://productforums.google.com/forum/#!topic/docs/83vr5nLeSp8;context-place=forum/docs
Count responses per hour in google forms spreadsheet
I have a google forms where I capture the user's email and I need to count how many responses are submitted per hour by each email (user). 

Forms enters a time stamp in the form of 

MM/DD/YYY HH:MM:SS

I assume that I just need to capture the HH part of the time stamp and count the occurrences for each email but I'm not sure how to do that. Any help? 

Thanks

Sunday, June 25, 2017

yogi_Count Instances Of Entities By Specified Age Groups

Google Spreadsheet   Post  #2193
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-25-2017
question by sarahcatherine:
https://productforums.google.com/forum/#!topic/docs/s_05I07vtGA;context-place=forum/docsFilling blank date cells with previous entry

Adding up instances in a spreadsheet that meet a certain criteria

I have a complicated spreadsheet issue... I am collecting a lot of data in a form. It includes names of individuals along with sex and birth dates.  I need an equation that can simplify the data and identify the number of adult males, adult females, children, babies and newborns based on the dates of births and sexes entered. Sample spreadsheet.

I think I need to use the countifs function but can't seem to get it work... 

Any ideas? 

Friday, June 23, 2017

yogi_Fill In Blank Date Cell With Previous Entry

Google Spreadsheet   Post  #2192
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-23-2017
question by REBECCA LEIGH CORREA:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/2YWx5Ean_7k;context-place=forum/docs
Filling blank date cells with previous entry
how to fill blank cells with date from the previous not blank cell.

yogi_Compute Days Elapsed Between Signing Of Contract And Implementation Date ...

Google Spreadsheet   Post  #2191
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-23-2017
question by erijohns:
Hi!

I've been reading through the Forum for the past couple days and learned a ton, but I haven't a seen a use case like this one and I'm stuck: 

My company has a product that can be implemented one of two ways. I have a spreadsheet with three columns: the Contract Signed Date, Go Live Date (Option A), and Go Live Date (Option B). 

Some clients have fully implemented our product, some have just implemented on one or the other, and some have only signed a contract -- they're being implemented but aren't live yet. 

I want to calculate how long, in days, it takes a given client to go live for the first time with our product, and if they aren't live yet, how long it's been since they signed the contract. I can figure out how to calculate how long it's been since Signed Contract, and I can figure out how to calculate DATEDIF between Signed Contract and one of the Go Live Dates, but if a client has fully implemented with us, then they have values in all three columns: they signed a contract, went live with Option A, and went live with Option B. 

So the trouble I have is: how do I create a formula that, if it sees two go-live dates (values in columns E, F and G), picks the one closest to the Signed Contract date and calculates how long it took to go live that first time? 

Here are the five scenarios I could think up: 

1. F=Blank, G=Blank (in which case, calculate E to Today)
2. F<>Blank, G=Blank (in which case, calculate E to F)
3. F=Blank, G<>Blank (in which case, calculate E to G)
4. F<>Blank, G<>Blank, F>G (in which case, calculate E to F, I think)
5. F<>Blank, G<>Blank, F<G (in which case, calculate E to G, I think)


It's possible that I'm not interpreting how a date could be less than or greater than another date. That's a bit confusing. 

Oh and here's some example data: 

1. E                   F                     G
  1/1/17  

The correct answer would be: 171 (when I posted this topic).

2. E                   F                     G
 1/1/17         1/2/17

The correct answer would be: 1

3. E                   F                     G
 1/1/17                                1/2/17

The correct answer would be: 1

4.  E                   F                     G
 1/1/17          1/5/17           1/4/17

The correct answer would be: 3

5.  E                   F                     G
 1/1/17         1/10/17          1/20/17

The correct answer would be: 9



Hopefully this makes sense. Here's my best attempt at a formula so far. It's so close, but I'm not expressing myself well enough. 

=IF(AND(F8<>G8<>“”,F8<G8),DATEDIF(E8,F8,“D”),IF(AND(F8<>G8<>“”,F8>G8),DATEDIF(E8,G8,“D”),IF(AND(ISBLANK(F8),ISBLANK(G8)),DATEDIF(E8,TODAY(),“D”),IF(AND(ISBLANK(F8),NOT(ISBLANK(G8))),DATEDIF(E8,G8,“D”),DATEDIF(E8,F8,“D”)))))


Thanks so much

Thursday, June 22, 2017

yogi_Query To Pull Select Columns Of A Large File To Be Brought In By using ImportRange Function

Google Spreadsheet   Post  #2190
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-22-2017
question by Sarvan Dharavath:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/5KFMydOQWhw;context-place=forum/docs

Filter only specific columns using importrange from one spreadsheet another spreadsheet

Hi, I am trying to import specific ranges from one spreadsheet to another using below formula (I found in Google forum), but it is not working(As it says "Result Too large") for me Please help. Below is my formula



=
QUERY(ImportRange("1evyx3-EHSIP6VRtisBBocvDqgLW_90SQWnej-i1Ug2w","Part Requirement!A:BZ"),"select Col1, Col3, Col4, Col5, Col6, Col8 where Col57 ='Need to be Delivered'")

And below are my sheets

Source sheet:

Target Sheet:
https://docs.google.com/spreadsheets/d/124p7VGvf8kmbJ1SikRtWNQ_tnFfy-Fn8cObOowZbzZ8/edit?usp=sharing

Wednesday, June 21, 2017

yogi_RoundDown Round And RoundUp Entries In A4:A

Google Spreadsheet   Post  #2189
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-21-2017
question by Robert Reiling:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/FZkqAiimEF0;context-place=forum/docs
How can I round down a number calclated to the next lowest 1/8 of an inch
I have a larege formula that the end result needs to be displayed as a fraction rounded down to the next lower 1/8 inch increment. I can not seem to find a number option for fractions or a round down formula to the next lower 1/8 inch increment.
Any help would be greatly appreciated. I know at minimum the fraction option is in Excel but have had no luck in sheets yet.
Thank you

Tuesday, June 20, 2017

yogi_Query Data Grouping By Month Names

Google Spreadsheet   Post  #2188
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-20-2017
question by Pashenkov:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/WpBZQU1j5ko;context-place=forum/docs
QUERY WITH DATE LOST FORMAT
Hello everyone,  I need help with a query which I want to show sum values and group by month,  here is what I got Until now
=QUERY(query(AdminOppImportageDB!$A$1:Q;"select J, sum(H),sum(I),sum(H)*I where (K <> 'Perdida' and Q=1) group by J,I ");"select Col1, sum(Col4) where Col2 is not null group by Col1 label Col1 'Valor de Oportunidades',sum(Col4) ' '")

but is seems to be incomplete due to there are months repeated at the result,

please help me out with this,

thanks