## Friday, June 30, 2017

### yogi_Compute Total Time Duration In hh:mm:ss For Set Of Times In Column A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-30-2017
question by Jikuu:
Calculating difference and sum on a SPLIT array

I'm trying to calculate the total number of hours between time ranges in multiple cells at the same time

 09:00-12:00 12:00-15:00 15:00-18:00 18:00-21:00 21:00-00:00

So first split on "-" to get the two times
=ArrayFormula(SPLIT(A1:A5,"-"))

Then calculate the difference between the two times and finally add them all up for the total number of hours. Not sure how to go about performing functions on all the results in an Array.

-Matt

## Wednesday, June 28, 2017

### yogi_Row By Row Sum Of Specified Number Of Non-Zero Entries

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-28-2017
SUM of values from first four columns that contain a value

I want to create a formula that will add up the values from the first 4 columns in a row that contain a value. I created this sheet to demonstrate the values I would want it to return.

## Monday, June 26, 2017

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-26-2017
question by zeke29:
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

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-23-2017
question by REBECCA LEIGH CORREA:
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 ...

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-22-2017
question by Sarvan Dharavath:

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

## Wednesday, June 21, 2017

### yogi_RoundDown Round And RoundUp Entries In A4:A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-21-2017
question by Robert Reiling:
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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-20-2017
question by Pashenkov:
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,

thanks

## Monday, June 19, 2017

### yogi_Match Last And First name Combinations In Another Sheet And Pull Corresponding Points

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-19-2017
question by J720:
How do I match data to a first & last name
I need help matching a specific number to a specific set of names. As you will see in the below link, I have three names separated by Last Name & First Name in "Sheet1". In Column D of "Sheet1" employees can accrue points, for example D2 for Jane Doe should have 12 points, D3 for Bill Zoe should have 8 points and Johnny Apple in D4 should have 3 points.

The points will be collected from "Sheet2" under cells associated with the "Sheet1" names. Without writing a formula like: =SUM(SUMIF(A:A,{"Zoe, Bill"},K:K)) that is written specifically for an employee's name, is there a way to match the points in Sheet1 column D to the names in Sheet1 Column A&B from the listed names in Sheet 2 row 1?

In Sheet2 row 1, the names are populated using: =Sheet1!A2&", "&Sheet1!B2

By applying this idea to Column D, adding and removing names to the entire workbook and formulas is simplified for me for the future.

I'd appreciate any suggestion.

## Sunday, June 18, 2017

### yogi_Pull Only Those Column Of Table Where Not All Rows of a Column Are Equal To Zero

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-18-2017
question by BgibbzGaminh:
Table Simplfier Function
Hi, so I do stats for a small time E-sports league, and I'm looking for away to condense player character selection tables. Essentially, I have a table of players and characters, recording the frequency that each player selects a character. However, many characters don't get selected. I want to create a formula to regenerate the table while only including columns that have a sum greater than 0 (Only include characters that were selected).

Linked below is sample doc showing what I would like to do.

### yogi_Split Row on New Line For All Entries In Column A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-18-2017

## Wednesday, June 14, 2017

### yogi_Key-in WorkDay Hours For Projects Given 'Start Date' 'End Date' And 'WorkLoad' Per Day

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-14-2017
question by Trevor Brightman:
Hello,
I'm looking for a way to automatically plot workload across a series of dates (calendar) so I can forecast the workload of a given day to assure our staffing levels will meet demand.

Attached is an example with the general idea.

Table 1 simply lists the projects with start and end dates, as well as a =NETWORKDAYS calculation of the number of week days between the start and end.

I also added a "workload" column (total hours for the project) and using the weekdays I calculated how many hours that project would be needed to be worked on each week day.

Here comes the tricky part. I want Table 2 to automatically plot the daily workload onto the calendar dates. I've filled in the data manually (in green) but I'm wondering if it can be done with some sort of formula.

Ideally this would allow me to quickly and easily tally the workload needed for various projects as workload and due dates change in Table 1.

I hope this makes sense, and I hope someone can help me out,

Thanks Internet Friends!