yogi_Compute Gestational Age In Weeks_And_Fractions_Of_Weeks Based On Due Delivery Dates

                                           Google Spreadsheet   Post  #1934
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 29, 2015
question by Eric Hawes:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/-arkdoM6cVg

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.

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

yogi_Split Cells IN Column A On Capitalization

                                           Google Spreadsheet   Post  #1933
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 27, 2015
question by David Lindamood:
https://productforums.google.com/forum/utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/GMhjp2kvVMM
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

                                           Google Spreadsheet   Post  #1932
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 27, 2015
question by Mar Cel:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/aaD4h6iGZcc
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.

https://docs.google.com/spreadsheets/d/163YM5GfsKrT87d5em8nLJQSAQpyvx0ZgNz-YrsLZCEg/edit?usp=sharing

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

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

                                           Google Spreadsheet   Post  #1931
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 26, 2015
question by JacksonIsaiah:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/xvbxh3bnSJw
Issue with Format of Form Response Data and Query

Greetings,

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

https://docs.google.com/spreadsheets/d/1PCkPa2SDde8kZwwgCnkmaAs7I7Af-jbAoVHeew5ZRH8/edit?usp=sharing

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

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

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

                                           Google Spreadsheet   Post  #1930
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 24, 2015
question by running_nexus5:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/CKlKE2IgSZo
Index/match or vlookup in this instance?

Hi everyone, I'm hoping someone here can help me figure this out. Here's my spreadsheet:

https://docs.google.com/spreadsheets/d/1y6fIetX890wUERodnoiBuBQ1Cbtb6d9rccbhq6XaLjc/edit?usp=sharing

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'

                                           Google Spreadsheet   Post  #1929
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 24, 2015
question by running_nexus5:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/CKlKE2IgSZo
Index/match or vlookup in this instance?

Hi everyone, I'm hoping someone here can help me figure this out. Here's my spreadsheet:

https://docs.google.com/spreadsheets/d/1y6fIetX890wUERodnoiBuBQ1Cbtb6d9rccbhq6XaLjc/edit?usp=sharing

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

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

                                           Google Spreadsheet   Post  #1928
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 23, 2015
question by Kaleb Captain:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/_DhDazbNfro

How to create list of unique values from range (column) and then display assoc. data (row)

My spreadsheet:

https://docs.google.com/spreadsheets/d/1p8j5-yhBRIKRYukbyaUFunHL7otGyg3GwE6S_LOGDYU/edit?usp=sharing

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

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

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

                                           Google Spreadsheet   Post  #1927
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2015
question by cacgas:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/cEOq4EmhQeQ
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

                                           Google Spreadsheet   Post  #1926
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2015
question by ari5000:
https://productforums.google.com/forum/#!mydiscussions/docs/S-yp2TqlTvQ
Help creating a Sum Filter Array MMULT formula in Sheets

https://docs.google.com/a/fivetowns.net/spreadsheets/d/1myKz4WpRAt3Kja__Dsddnr3YtfOjVPkYUOTXoi-4exc/edit#gid=1665161315 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. -------------------------------------------------------------------------------------------------------------------------

yogi_Pull Into Another Sheet Information About Unsafe Zones And Associated Activities

                                           Google Spreadsheet   Post  #1925
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 16, 2015
question by Clay Rollo:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/fDP9bbMgSNk
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.....

   Thank you in advance for any help you can provide, I appreciate it!

---

Images are not displayed

Display images in this post - Always display images from Clay Rollo - Always display images in Google Docs Help Forum

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

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

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

                                           Google Spreadsheet   Post  #1924
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 16, 2016
question by LCHM:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Kuotlw1crJM
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'

                                           Google Spreadsheet   Post  #1923
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 16, 2016
question by Chris Hodas:
https://productforums.google.com/forum/#!mydiscussions/docs/X3MAZC4A8ns
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.

https://docs.google.com/spreadsheets/d/1R4aKPlqMbR2LnLKUvEt61WRaQDjXzMiC6Cbd9RyyxKQ/edit?usp=sharing

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

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

yogi_Sum Numbers Given As Integers And Decimal Fractions (POUNDS decimal_point OUNCES)

                                           Google Spreadsheet   Post  #1922
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 15, 2015
question by sedgley wmac:
https://productforums.google.com/forum/#!mydiscussions/docs/NOsEtAULLQQ
how do i add lb's and oz's in google spread sheets

hi I am starting a spread sheet to display a league table for a fishing club

how do I add up lb's and oz's 

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