yogi_Conditionally Format matching and Non_Matching Entries In Columns A and B

Google Spreadsheet   Post  #2559

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-31-2018

question by: Ari R:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/DA2kzqnBsF4;context-place=mydiscussions
Google Sheets matching entries highlighted
I need all matches in Columns A & B to be highlighted in green and which don't match to be highlighted in Red.

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

yogi_Pull Hours data from Column G of spreadsheet 'Schedule' matching it with 'Date' 'Provider' 'Start Time' and 'Start Location

Google Spreadsheet   Post  #2558

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-31-2018

question by: NJP2017:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/LJDMT51jGWY;context-place=mydiscussions
Can I do a multi paramater index match with one parameter searching a range of values?

I'm not sure if what I am trying to do is even possible but I thought I would bring it here in case anyone can help. I an working with two different Google Sheets documents. One is Audit (https://docs.google.com/spreadsheets/d/1VCuhiYd01pgQKYWWTbXo5mYxW4wsFGe66Wf9jN8O4ng/edit#gid=0) and the other is Schedule (https://docs.google.com/spreadsheets/d/1p65lN0FzxrscEAAEmb_Ohga-XgMSSWXirts5UI1LgRU/edit#gid=0).

I am trying to pull the hours data from the Schedule doc (column G) and match it with the date, provider, start time and start location. However, the start location names aren't consistent so I have the alternate names on the Start Location tab of the Audit sheet. I was trying to just do the range part using another help page (https://stackoverflow.com/questions/14803944/excel-searching-for-multiple-terms-in-a-cell) but I couldn't even get that part to work.

Is there a way to do this or a better method anyone would suggest without altering the data (which I can't on the actual documents).

yogi_Count PostCodes Based On Just The First Letters

Google Spreadsheet   Post  #2557

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-30-2018

question by: Jack Supertramp102:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/zoul4JWWb2c;context-place=mydiscussions

How do i count postcodes based on just the first letters issue

Hi Everyonre,

I have a spreadsheet where i log postcodes at certain times. I need the spreadsheet to count the post code types based on the first letters of the postcode. It does this already but the issue is if i have a postcode in my log L6 and also LL6,, the spreadsheet thinks there is 2 L postcodes. Another example is i have postcode SK7 and S3. The spreadsheet thinks there is 2 S postcodes because they both start with S. I need each postcode type to be counted seperately without this issue.

Spreadsheet sample link below:

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

Hope you can help,

Kind Regards,

Jack

yogi_Pull Heater On/Off Data From Sheet1 And Compute Heater On Duration In Minutes

Google Spreadsheet   Post  #2556

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-28-2018

question by: Mark Allenspach:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/A2xTirWzmx4;context-place=mydiscussions

Removing bad data

I have various heater elements that collect data in a google sheet to date/time stamp when the heaters turn on/off.

A sample that can be viewed is at https://docs.google.com/spreadsheets/d/19vaPCacy7QmMCvNp4oLSgX2j22Ff2321Cc51OkK6kLk/edit?usp=sharing

Normally, the data will show the first time stamp when the heater turned on followed by the next time stamp when it turned off.

Per the snapshot, sometimes I get bad data and have several time stamps in sequence all saying the heater turned on (or off).

When there is such erroneous data, I would like to only keep the 1st time stamp for "ON" and the last time stamp for "OFF".

Capture.JPG

92 KB

yogi_Conditionally Format Lowest Number in Columns D:Z

Google Spreadsheet   Post  #2555

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-23-2018

question by: topdog2009:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sHFjwihqUK4; context-place=forum/docs

I have a series of columns say D:K

and a series of rows with numbers say 4:23

Each column of numbers is unique.

I want to highlight the lowest number in each column providing it is not blank or zero.

Additionally, the number of columns may increase say to Z

Conditional formatting.

yogi_Compute Stats For Entities From Different Tabs Of A Spreadsheet

Google Spreadsheet   Post  #2554

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-28-2018

question by: BrokerMan88:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/3k8dZq-5yHY;context-place=mydiscussions

Pulling customer names from one sheet to another
a & b) I am trying to have customer names appearing on my sales sheet (Sales!C:C) automatically added [once] to my customer list (Customers!B:B)

c) I have added example entries into my spreadsheet. If I added a new sale for a new customer (lets say the new customer is Customer E) into Sales!, I would want "Customer E" to appear on Customers! in column B

For you to see the spreadsheet in question, should I share with you or is there a way to share it on this thread?

yogi_Working With And Summing Times That Contain MilliSeconds

Google Spreadsheet   Post  #2553

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-27-2018

question by: Mick Stringfellow:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/AcqUvzZg6uo;context-place=forum/docs

Add together Hours, Minutes, Seconds & Milliseconds.
Hi all,

I'm after a solution to add together four times.  The problem is the times are Hours, Minutes, Seconds and Milliseconds.  I understand Google Sheets doesn't recognise milliseconds so i'm a little bit stuck.

This link takes you to a google sheet I've created which has an example of the problem i'm having and explains a bit more.

I hope you can help.

Thank you,

Mick

yogi_Compute Row By Row Sum Of Hours By Entity From Data In Multiple Tabs Of A Spreadsheet

Google Spreadsheet   Post  #2552

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-27-2018

question by: A Guy:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/dUHlRRiKhzk;context-place=forum/docs

Array within Formula ARRAYFORMULA(SUM(QUERY())

I've read through threads on how to array formulas but I'm failing to understand how to write my formula. What I have in place works for the first row but doesn't continue down the column. It works to give me the correct data in the first row, but doesn't go down the column no matter how I write it.

Link to example sheet

here

My Formula

=ArrayFormula(SUM(query(DB!A1:L,"select (E-D)*24*4 where A='"&A3&"' and B='"&B3&"' and C>= date '"&text(C3,"yyyy-MM-dd")&"' and C<= date '"&text(D3,"yyyy-MM-dd")&"' Label(E-D)*24*4 'Total Quarter Hours'",1)))

yogi_Multi-Conditional Count Computation

Google Spreadsheet   Post  #2551

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-24-2018

question by: Rhian H:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/KdB8nIUjrlc;context-place=forum/docs

How can I count cells in a row that contain 2 different criteria in 2 different columns
My database has 2 parameters that must be met in order to make a count.   I want to count the number of times an instructor taught in a month.  One column lists that date (day month date and year) and the next lists the instructor.  I cant seem to get the formula to count if both criteria are met.  So for example I have:

Friday, November 2, 2018	WENDY
Saturday, November 3, 2018	RUMI
Monday, November 5, 2018	RHIAN
Wednesday, November 7, 2018	MICHELLE
Friday, November 9, 2018	MICHELLE
Saturday, November 10, 2018	WENDY
Monday, November 12, 2018	RHIAN
Wednesday, November 14, 2018	RUMI
Friday, November 16, 2018	MICHELLE
Saturday, November 17, 2018	WENDY
Monday, November 19, 2018	RUMI
Wednesday, November 21, 2018	RHIAN
Friday, November 23, 2018	MICHELLE
Saturday, November 24, 2018	WENDY

I want to count the number of times Michele taught in November.  I'm using this formula 

=COUNTIF(A11:B60, "*November*" + COUNTIF(A11:B60, "Michelle"))

but its not picking up anything. What am I doing wrong??

Please help!

yogi_Compute Cumulative Sum Of Entries In Every Third Column Of B1:1

Google Spreadsheet   Post  #2550

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-24-2018

question by: Quxzcover:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/9sUNBn2PNeQ;context-place=forum/docs

How to Make Formula Only Sum Every Third Column?
I'm currently using this formula: ArrayFormula(IF(Len(I12:12), (sumif(Column($I12:12), "<="&column(I12:12), $I12:12)),)

How would i get it to only look at the numbers in every third column. Here is a spreadsheet showing the problem in more detail:

https://docs.google.com/spreadsheets/d/1qcWnobJOqf6I2fpDSdQkwzUhwLPBc58lQ_TgeMvt3IQ/edit?usp=sharing
Link to where the original formula came from:
https://productforums.google.com/forum/#!topic/docs/m-oqIc2uO7U;context-place=mydiscussions

yogi_Compute Numerator Of A Fraction As If The Denominator of The Fraction Were Equal To 20

Google Spreadsheet   Post  #2549

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-24-2018

question by: Kyriacos Achilleos:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/bC0Vt7zur5c;context-place=mydiscussions

How to stop simplifying fractions or convert text into fractions

I want to be able to enter fractions into a cell and then use those fractions as part of a formula in another cell. 

• If I don't use the formula sign (=) at the beginning of the cell, the fraction is displayed exactly as I enter it because it is treated as text (e.g. 5/20). However, in this case I cannot use it in the other cell as part of a formula because it is not recognized as a number and using =VALUE(cell) returns an error.
• If I enter =5/20 then the fraction simplifies to 1/4 and the formula works correctly. However, I don't want the value to be simplified. I want it displayed exactly as entered. 

I have tried several custom number formats with no luck. Any help would be appreciated.

Kyriacos

yogi_Working With Fractions Entered In Google Spreadsheet Cells As Text

Google Spreadsheet   Post  #2548

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-22-2018

question by: Kyriacos Achilleos:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/bC0Vt7zur5c;context-place=mydiscussions

How to stop simplifying fractions or convert text into fractions

I want to be able to enter fractions into a cell and then use those fractions as part of a formula in another cell. 

• If I don't use the formula sign (=) at the beginning of the cell, the fraction is displayed exactly as I enter it because it is treated as text (e.g. 5/20). However, in this case I cannot use it in the other cell as part of a formula because it is not recognized as a number and using =VALUE(cell) returns an error.
• If I enter =5/20 then the fraction simplifies to 1/4 and the formula works correctly. However, I don't want the value to be simplified. I want it displayed exactly as entered. 

I have tried several custom number formats with no luck. Any help would be appreciated.

Kyriacos

yogi_Counting Entries In A Filtered Table That Meet Specified Criteria Using SUBTOTAL Function

Google Spreadsheet   Post  #2547

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-18-2018

question by: Perk1961:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/bC0Vt7zur5c;context-place=mydiscussions

Countif greater than with hidden rows

I am trying to count the how many times the number 4500 or greater appears in 5 separate columns. I can do this in excel using Sumproduct with subtotal and offset in the string,  but the same formula does not translate to Google Sheets. I have searched tirelessly online but can't find a solution. Any help would be appreciated.

See sample sheet file below.

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

Note: The formula in cell B4 works in excel, which is =SUMPRODUCT((B7:F19>=4500)*(SUBTOTAL(103,OFFSET(B7,ROW(B7:F19)-MIN(ROW(B7:F19)),0))))

Thanks in advance.