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

### yogi_Count Number Of Holidays And WeekEnds That Coincide With Holidays

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

## Monday, June 12, 2017

### yogi_Conditionally Format Cells E7 To EY33 If Entry In Column E is 'Complete' and Entry In Column F Through EY is 'X'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-12-2017
question by Matthew Dengler:
Dynamically Changing Conditional Formatting in Sheets
Hi everyone,

I would like to apply a set of conditional formatting logic across a group of cells, but have the rows and cell numbers change based on the cell that I "drag/copy" the conditional formatting to.

To be clear, I would essentially like the conditional formatting logic with the following formula...

=AND(E7="some text",K7="some other text")

...To be applied to a large number of cells. But I cannot just apply the above formula to all cells, I need the rows and cell values formula to change dynamically based on each cell that I apply it to (as if I had dragged a normal formula across a group of cells).

To be clear again, I need the above formula to change to =AND(E8="some text", K8="some other text") and =AND(E7="some text",L8="some other text") when I apply it to different rows and columns.

Can anyone help me with this?

## Sunday, June 11, 2017

### yogi_Mark The First Instance Of An Item As OK And All Subsequent Instances of the Same Item As DUPLICATE

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-11-2017
question by Aleksandar Arsovski:
Hi I need help with to modify arrayformula for Duplicate check up.

in B1 is my arrayformula which not functioning good, since I want first instance of the duplicate value to have Ok not Duplicate.

In C2 to C21 is how I want the result to look, and the formulas there are working as I want but I doesn't know how to make it work in Array Formula.

Any help appreciated.

Regards,
Aleks

## Saturday, June 10, 2017

### yogi_Based On Form Input Make A Computed Column For Gender Based ID For male and Female Students

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-10-2017
question by Ovation Theatre:
Hello! I am a high school theatre teacher looking to utilize Google Forms for our musical auditions. I was wondering if it is possible to create a unique ID based on the gender of the respondent. For example, I may have three male students sign up, and they are assigned M001, M002, and M003 based on marking "Male" as their gender on their forms. Similarly, when female students sign up, they are assigned, F001, F002, etc.

Is this possible? This would save me an enormous amount of time if we already assigned audition numbers prior to the audition

## Friday, June 9, 2017

### yogi_Pull Name From A2:A51 For Specified Debate Type In E2:E51

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-09-2017
question by Jon Baney:

# IF or Lookup function (If false, find next true cell)

Okay, here it goes. This is as much as I can get you, it isn't the actual sheet that I will be using, but a copy (I'm a teacher and for one, I cannot just publish student info like emails to the web, and for two...this is something that I'm creating now and won't have real data for until schools starts, so the data is fake, but it is realistic to what I need):

I have a line inserted between row 52 and 53 which divides the "tables". The lower table is based on responses in the top table (I am trying to get lists of student names who are competing in each event). I typed into each cell on row 54 what the function for that column from that point down should be looking for. To demonstrate what the data should look like as a finished product, I manually input what the table should look like after the formula is applied to each column. This "desired data" starts on the next row, 55, and continues down until in each column until there is no more "true" responses to the function.

I think I provided you with what you requested. As far as the logic goes for why I need this, I need to be able to be able to view all of the input from the top table as-is (it is actually a query pulled from another sheet that will be linked to a google form and will update as students "sign up" for each tournament) because I need to be able to view that data exclusive to that student for various reasons. I need the bottom table because I need to be able to easily see how many students are competing in all of the events without having to a) reorder the sheet or b) pull all of the responses to show and have 50+ responses under each event some of which says "student name from column A" and the rest saying "false". Starting on row 100 I have an example of what I WAS able to get, but you will see why that is undesirable...I want each column to only show the "true" responses--though, in collumn 101D-101L, I am not sure how to make those formulas search for a phrase within text and kick back a true statement if true, which is what I want (and is my second question listed in my original post). Example desired formula: Give me the value for column A any time column F contains "informative speaking":

Cell F="Informative Speaking, Original Oratory" (THE FORMULA IS TRUE!)
Cell F="Informative Speaking" (THE FORMULA IS TRUE!)
Cell F="Original Oratory, International Extemp" (THE FORMULA IS FALSE; this cell would show the next true statement.)
Cell F="International Extemp, Informative Speaking" (THE FORMULA IS TRUE; this would actually appear in the cell above because it was false)

I hope I am explaining this all correct and that it can all be done.