## Sunday, March 29, 2015

### yogi_Compute Gestational Age In Weeks_And_Fractions_Of_Weeks Based On Due Delivery Dates

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 29, 2015
question by Eric Hawes:
Calculation of gestational age
I have a table with due dates in the format of mm/dd/yy in column b and would like to calculate gestational age in column c in the format of weeks and days gestation
(i.e. 36 3/7 weeks).

Can someone help me with a formula that I can apply to all of the cells in column C?
Thank you.
-------------------------------------------------------------------------------------------------------

## Friday, March 27, 2015

### yogi_Split Cells IN Column A On Capitalization

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 27, 2015
question by David Lindamood:
Split a cell based on capitilization
I have cell A1 that contains Ca3(PO4)2     I would like to split the string in the cell to : cell B1 = "Ca3", C1 = "(P", D1 = "O4)2".

But I would like it to be able to handle other strings so that it is split  between capital letters (without loosing the capital letters)
I ultimately want to find a count of each element in the chemical formula.  elements in a chemical formula are either a capital letter or a capital and a lower case letter.
-----------------------------------------------------------------------------------------------------------------------

### yogi_Delineate MakeUp of Teams With 1 Player From Each Of Tiers Specified in Cell D3 and Number Of Students Listed In Cells A2:A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 27, 2015
question by Mar Cel:
Random selection of team members based on tiers
I'm trying to figure out how to randomly select students to assign to a team but based on one student from each tier. So a student per tier per team. If there are extra students then a last team is based on students from the last tier.

There's a manual example in this editable sheet below.
---------------------------------------------------------------------------------------------------------

## Wednesday, March 25, 2015

### yogi_From Table Of Timestamps And Response Types Compute Number Of Responses Per Hour By Day

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 26, 2015
question by JacksonIsaiah:
Issue with Format of Form Response Data and Query
Greetings,

Here is a sample sheet with Form Responses I am working on:

Thanks to all the help and information on these forums, I've learned a lot about sheets, and found this formula from Hyde (a big forum contributor) which should show the number of responses per hour:

=query( arrayformula( if({1,0}, A2:A, A2:A) ), "select toDate(Col1), count(Col2) where hour(Col1) >= 0 group by toDate(Col1) label toDate(Col1) 'Date', count(Col2) 'Number of responses' ", 0)

I'm getting a #VALUE error which states the query can't perform the hour function on a column that isn't TimeOfDay or DateTime. I've tried several number formats but can't seem to get it working.

Ideally, the query would show the number of responses per hour by day.

Any help would be greatly appreciated.

-Jackson
--------------------------------------------------------------

## Tuesday, March 24, 2015

### yogi_Conditionally Format Rows In Column A Where None Of Corresponding Alternate Names In Sheet 'NAMES' Is In Sheet 'DATA1'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 24, 2015
question by running_nexus5:
Index/match or vlookup in this instance?
Hi everyone, I'm hoping someone here can help me figure this out. Here's my spreadsheet:

What I have is data from a couple of different sources about people, and occasionally I'll need to cross-check between sheets. But because sometimes the names aren't an exact match (sometimes the sources use a different name for the same person) I maintain a "NAMES" sheet to record name variations. In this example, DATA1 uses the name Joseph Richards and DATA2 uses the name Joe Richards. As you can see, NAMES shows that "Joe Richards" is an alternate name of Joseph Richards, so they are a match. I'm trying to identify records in DATA2 where the person is not found in DATA1 after cross-checking the alternate names in NAMES.

In this example I want to somehow flag Joel Richards as a record in DATA2 because he is not found in DATA1 even after cross-checking NAMES. Joe Richards (DATA2) would not be flagged because he is found in DATA1 after cross-checking NAMES.

Hopefully I have explained this sufficiently. If anyone here can help me out I'd be eternally grateful! :)

### yogi_Conditionally Format Rows In Column A Where Name Does Not Match Any Of The Names In Table In Sheet 'NAMES'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 24, 2015
question by running_nexus5:
Index/match or vlookup in this instance?
Hi everyone, I'm hoping someone here can help me figure this out. Here's my spreadsheet:

What I have is data from a couple of different sources about people, and occasionally I'll need to cross-check between sheets. But because sometimes the names aren't an exact match (sometimes the sources use a different name for the same person) I maintain a "NAMES" sheet to record name variations. In this example, DATA1 uses the name Joseph Richards and DATA2 uses the name Joe Richards. As you can see, NAMES shows that "Joe Richards" is an alternate name of Joseph Richards, so they are a match. I'm trying to identify records in DATA2 where the person is not found in DATA1 after cross-checking the alternate names in NAMES.

In this example I want to somehow flag Joel Richards as a record in DATA2 because he is not found in DATA1 even after cross-checking NAMES. Joe Richards (DATA2) would not be flagged because he is found in DATA1 after cross-checking NAMES.

Hopefully I have explained this sufficiently. If anyone here can help me out I'd be eternally grateful! :)
------------------------------------------------------------------------------------------------
following is one way using Conditional formatting approach

## Monday, March 23, 2015

### yogi_Select Specified Number Of Card Nos and Corresponding Characters for 'X-Men Card Dice Game'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 23, 2015
question by Kaleb Captain:
How to create list of unique values from range (column) and then display assoc. data (row)

I attempting to create an automated way to draft random characters in an X-Men card/dice game.  You can see my headway on sheet 'RandomCharacters' where I generate a list of random characters.  However, multiple characters exist for each character type (see below).

Sample Data:
 Card # Character Name 1 Angel Air Transport 2 Angel Inpiring 3 Angel Superhero 63 Angel Flying High 35 Ant-Man Biophysicist 64 Ant-Man Pym Particles 95 Ant-Man The Insect World ... ... ... 22 Wolverine The Best There Is 23 Wolverine Not Very Nice 24 Wolverine Superhero 92 Wolverine Antihero 61 X-23 Scent of Murder 93 X-23 Assasin 121 X-23 Killing Machine

So now on sheet 'UniqueCharacters' I am trying to constrain the list to unique character types (column B). For example, if "Angel - Air Transport" has been drafted then I want "Angel - Inspiring" as well as any other "Angel" characters to be excluded.

Current Output (undesirable duplicate characters):
 Angel - Air Transport (1) Angel - Inpiring (2) Black Panther - Wakanda Chief (38) Cable - Time Traveler (99) Captain America - "Follow Me!" (69) Captain America - Special Ops (40) Cyclops - Optic Blast (4) Falcon - Samuel Wilson (42)

Any help would be greatly appreciated!  Be forewarned, I am much more a script kitty than a mathematician, so thanks for your patience as well.

- KC
-----------------------------------------------------------------------------------------------------------------------------------------

## Wednesday, March 18, 2015

### yogi_Provide in Column B Row By Row Six Digit ID Based On Sequential Date Entries in Column A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2015
question by cacgas:
Need a formula for where the result is a six digit combination of the month & entry count
I have a results SHEET that populates from a FORM.  I would like to create a column in which the result yields a unique SIX DIGIT number derived from:
1). The MONTH of a date that is entered from the FORM (not the time stamp)  AND
2). The count of which entry that was

E.g: The 38th entry with a date entered as 03/18/2015 would yield the following result:  030038
The 7th entry with a date enterd as 04/14/2015 would yield the following result: 040007

Help much appreciated!
---------------------------------------------------------------------------------------------------------------------------

### yogi_Conditionally Format Cells In Column I If Row By Row Sum Of Cumulative Hours By Date And By Client Is Over 6

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2015
question by ari5000:
Help creating a Sum Filter Array MMULT formula in Sheets
 Above is link to mock up of my spreadsheet. I would like to build a formula that SUMS up total hours (G) each day (A), for each unique client (D). This has to be an array formula, I think, because new entries are added each day. I have tried to create a sum filter array formula that uses mmult function but failed. Basically, the formula needs to check for identical dates, then match them with each unique set if identical clients, then once it groups each unique set of dates with each unique set of clients, add up the total hours and see if it's over six. After hours of research all I came up with was this and it obviously does not work but I think it's sort of on the right track: =SUM( FILTER( ARRAYFORMULA( MMULT( (\$G\$2:\$G=TRANSPOSE(\$A2:\$A)) * TRANSPOSE(\$D2:\$D) , SIGN(ROW(\$G2:\$G)); ???)))) The goal is not to print any totals, but simply to create an alert if the total is over 6 hours. -------------------------------------------------------------------------------------------------------------------------

## Tuesday, March 17, 2015

### yogi_Pull Into Another Sheet Information About Unsafe Zones And Associated Activities

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 16, 2015
question by Clay Rollo:
Formula to pull information from multiple cells to another sheet, based on certain data
Hello,

I have a form response sheet for a safety audit.  There are quite a few cells, and many audit teams.  It takes a lot of scrolling back and forth to see all the data.  I need to be able to import any "unsafe" findings, along with the audit team, zone audited, and date of the audit to another sheet.  I only need to see the information if an "unsafe" was reported, and even then, all I want to see is what was unsafe, who found it, when it was found, and where.  I have made a shorter example sheet to hopefully help explain what I mean, you can see it HERE.  Please note that there are several different "topics" to the audit (i.e. general safety observations, forklift safety, employee behavior, etc. and each topic has a comment section at the end....).  I would like to import the comments only if there is an "unsafe" in that section.....

---
Images are not displayed
Display images in this post

To clarify (or maybe I've just thought it through a little more), if the information below is on sheet "Form Responses 1", and any cell (E2 for example) contains the word "unsafe", then I want to copy the information in columns A-C for that row, as well as the header for the column with "unsafe" (E1 in this example) to a second sheet, i.e. a sheet called "Unsafe Findings".  In addition, I would also like to copy over any comments (G2 in this example) to the other sheet, but only if there is an "unsafe" in that topic (in my original question I mentioned that I have several "topics" separated by comment sections)....

Again, I REALLY appreciate any help
----------------------------------------------------------------------------------------------------------------------------------

## Monday, March 16, 2015

### yogi_Compute Row By Row Corresponding Column Number For Upto Two Letters At End Of String In Column A

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 16, 2016
question by LCHM:
Return letter from string and determine alphabet count
I am trying to extract the last character of of a string which could be either a number or a letter. If the character IS a letter (A-Z), I want the formula to return what number that letter is in the English alphabet.

For example I have the following data in A1-A3:

DH000169-192N
DH000193-222
DH000223-265D

In cell B1 I would like the formula to return the "N" as 14 (the 14th letter in the alphabet)
Whereas in B2 I would like the formula to display nothing, or "False" (as there is no letter character at the end of the string.
In cell B3 I would like the formula to return the "D" as 4 (the 4th letter in the alphabet)

Any thoughts on this one?
---------------------------------------------------------------------------------------------------------------------------

### yogi_Filter Values In Table A Where Date In Column C is Greater Than or Equal TO 'TODAY_Date - Specified_Number_Of_Days'

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 16, 2016
question by Chris Hodas:
How to filter a column with dates in the last 30 days
Hello,

I have a spreadsheet with a date column (Reported Date) that contains log entries of several months worth of data.  I would like to be able to filter the log list to show entries that have a reported date within the last thirty days.  Is there a way to do this with Sheets?  I was able to accomplish this in Excel using macros.

Any help is appreciated!

Thanks,

Chris Hodas
---
Here is the link to the sheet in question.

There is only one sheet and it is called AV Log.  I want to place a filter on column C (Reported Date) that will allow me to show only log entries that have happened in the last X days where x for trial purposes can be 2 days for now.  So when the filter is applied I should only see entries from today (3/16/15), yesterday (3/15/15), and the day before (3/14/15) anything before that would be hidden. So with the filter in place I would expect to see only 4 entries on the log.

Thanks for you help!

Chris Hodas
----------------------------------------------------------------------------------------------------------------------------------