yogi_Compute Difference In Hours And Minutes Between Planned And Actual Time Of Operation

Google Spreadsheet   Post  #2356

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

question by Bryan Columbus

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/2DYUCgrFP2A;context-place=forum/docs
Help with Google array formula for duration between two times

Help with Formula Google sheets for duration between two times. Array Formula that can:

1. Calculate the duration between times if the End time is after the Start time

2. Calculate the duration between times if the End time is before the Start time

3. Calculate the duration between times if the start time is before midnight and end time is after midnight or vise versa

I tried and searched for hours to have one formula for this. This is as close as I could get so far:

=ARRAYFORMULA(if(len(C2:C)*len(D2:D),D2:D-C2:C,)) 

Results:

     C                   D                          E (Formula)       C and D are time format  E is format [H]:MM

10:00 PM        10:30 PM                    0:30

11:00 PM        10:30 PM                  -0:30

1:30 AM          10:30PM                    21:00

Tried many other formulas, nothing did it yet

Any help would be really appreciated thanks

yogi_Conditionally Format Column by Column First Row From Bottom That Says FILLED

Google Spreadsheet   Post  #2355

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

question by ScienceKirby Reborn

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/R8NVbrDPvR0;context-place=forum/docs
Conditional Formatting To Highlight One Cell In Row With First Instance of String

Screen cap shows what I want to do.

I tried using COUNTIF(, AND(, and nested IF statements but can't seem to get what I want.

Capture.PNG

yogi_Compute Number Of Days Taken For A Stock To Recover From Price Drop On A Specified Trading Day

Google Spreadsheet   Post  #2354

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

question by Tomhawk

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/1otdKQaIycE;context-place=forum/docs
Days to recover from a price drop
Hi All,

I wanted to ask if anyone here knows the problem that i ran into.

I'm trying to find out how many days does a stock take to recover from a price drop (If there would be in within given number of days)

Say =GOOGLEFINANCE("AAPL","close","12/22/17","12/22/17"+25)

You get the following

Date	Close
12/22/2017 16:00:00	175.01
12/26/2017 16:00:00	170.57
12/27/2017 16:00:00	170.6
12/28/2017 16:00:00	171.08
12/29/2017 16:00:00	169.23
1/2/2018 16:00:00	172.26
1/3/2018 16:00:00	172.23
1/4/2018 16:00:00	173.03
1/5/2018 16:00:00	175
1/8/2018 16:00:00	174.35
1/9/2018 16:00:00	174.33
1/10/2018 16:00:00	174.29
1/11/2018 16:00:00	175.28
1/12/2018 16:00:00	177.09

My questions is: say take Date 12/22/17 Apple was trading at $175.01. By looking at the chat above, it takes approximately 11 trading days, (1/11/2018) to fully recoverd $175.28. 

It would be wonderful if there is a find out the number of days it will take to get close to where it was within +-2%. in this case will be 1/5/2018 traded at $175. 1 cent slightly below $175.01 comparing to 12/22/17. 

Thanks a lot!

yogi_Monthly Calendar For A Year With Week Beginning With Any Day Of Week And Any Month Of Year

Google Spreadsheet   Post  #2353

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

http://yogi--anand-consulting.blogspot.com/2018/01/yogimonthly-calendar-for-year-with-week.html

yogi_Work Around For Using An ArrayFormula Where Filter Function Is Being Used

Google Spreadsheet   Post  #2352

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

https://productforums.google.com/forum/#!topic/docs/EG_7crFT6q8;context-place=forum/docs

Hi all, can anyone give me a hint with this?

I have this fórmula

=IFERROR(VLookup(TODAY(),Filter('Inventário'!$A2:$C,'Inventário'!$B2:$B=$A2),3,1),0)

I would like to make it and arrayformula, just like this:

=IFERROR(VLookup(TODAY(),Filter('Inventário'!$A2:$C,'Inventário'!$B2:$B=$A2:$A),3,1),0)

I know that FILTER doesn't work with ARRAYFORMULA, and am also aware that VLOOKUP does. My problem is that i haven't figure out a way of transforming that FILTER into and WORKING VLOOKUP.

Any help/hint would be much appreciated.

yogi_Using COUNTIF For Mult-Criteria Count With Data In More Than One Sheet

Google Spreadsheet   Post  #2351

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

question by: Ulises HM

https://productforums.google.com/forum/#!topic/docs/XnPri_9E-oY;context-place=forum/docs

Countifs with month
Hello,

I have a Master data sheet called Donées. In column C, I have the dates when the questionnaire was filled out. 

In the tab Janvier, I would like to pull specific data of all the different FOSAs based on the month of January but I keep getting errors. Please see formula in cell B3 of tab Janvier. Can anyone help me out?

Here is the Link to my sheet.

Thank you in advance

Ulises 

yogi_Conditionally Format Rounded Numbers To OP's Specs

Google Spreadsheet   Post  #2350

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

question by: BPD FTO

https://productforums.google.com/forum/#!topic/docs/MCZPySiAkIc;context-place=forum/docs

Conditional Formatting on averaged numbers

SO I have a formula that averages numbers (it works great)

I have the decimal set so there is noting displayed after the number so it round up just like I want it do to so if my answer is 1.9333 it will display a 2

the problem is when I try to set conditional formatting

I want all 2 to have a yellow background 3 green and 1 red

Google looks at the entire 1.933 number instead of the 2 so it changes the color to red instead of yellow

I have tried text is exactly conditional formatting and it does not work appropriately.

I have tried the in between one also but it still looks at the 1.933 # instead of the 2 which is displayed in the cell

any ideas

yogi_Compute Average Subject to Specified Criteria For Each Student From Data In Another Sheet

Google Spreadsheet   Post  #2349

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

question by: Mark Engler

https://productforums.google.com/forum/#!topic/docs/GOxENbalfmA;context-place=forum/docs

Query by date, average, order by student, with one result per student
Hello,

I have this spreadsheet set up to import form responses that show student performance over a date range:

https://docs.google.com/spreadsheets/d/17DJzBv62r8ADU1qpzZyK5vaW1IjfRmh2f8ZfWgyJC3U/edit#gid=1636722914

I am trying to set up the sheet so that the "Query" tab pulls information from the "Progress" tab, providing one result per student/goal, and their average performance over a date range, based on a cell value (G1), using this formula:

=QUERY(Progress!B:E,"select B,C,D,E avg(E) > date '"&TEXT(G1,"yyyy-mm-dd")&"' order by B desc", 0)

Any help is greatly appreciated!!

Best,

M

yogi_Troubleshoot And Fix Problem With Date String In Cell A2

Google Spreadsheet   Post  #2348

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

question by: Chris B M

https://productforums.google.com/forum/#!topic/docs/LFkyd3eFCpo;context-place=topicsearchin/docs/category$3Aspreadsheets%7Csort:relevance%7Cspell:false

Date text not being recognized as a date (i.e. Jan 01, 2018)

I'm trying to parse some text that gets automatically imported into my spreadsheet as a date, but that text isn't being recognized correctly. However, when I go to a blank cell and type the exact same value, it's being recognized as a date. Any ideas.

Here are two examples that I can't the spreadsheet to recognize as dates.

M‌a‌r‌ ‌1‌6‌,‌ ‌2‌0‌1‌8‌

=datevalue("M‌a‌r‌ ‌1‌6‌,‌ ‌2‌0‌1‌8‌")

Here's a link to a spreadsheet that shows the issue. Two identical formulas (as far as I can tell) get different results.

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

yogi_Compute Row By Row Running Count OF Genres Read By Name

Google Spreadsheet   Post  #2347

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

question by: Mike Wielgus

https://productforums.google.com/forum/#!topic/docs/MAOBOsBw-Pg;context-place=mydiscussions

Counting Unique Book Genres Per Student Form Submission

I am trying to keep a running total of unique book genres that students read.

Students will fill out a Google Form and for each submission that has a unique genre, I'd like the cell in the sheet to increase.

So, the first unique genre's cell would read 1, the second unique genre's cell would read 2.

When a different student submits, it would count that student's unique genre starting at 1 and increase with each new submitted genre.

Can anyone out there assist?

Here's a link to the Google Sheet.

https://docs.google.com/spreadsheets/d/1d-DYW2cTsNhvPuwCjqWRwTeoUhdUoS2OiHszAjMq5kY/edit#gid=361458343

Thanks,

Mike 

yogi_Conditionally Format Column A If Neighboring Column B Houses Yes or ✔

Google Spreadsheet   Post  #2346

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

question by: ship

https://productforums.google.com/forum/#!topic/docs/T6m4gJ__M-8;context-place=forum/docs

Is it possible to create Conditional Formatting that uses a neighbouring cell's value?
Hello 

Is there any way to create conditional formatting that depends on a cell OTHER than the one being formatted?

i.e. I want to change the formatting of a cell if the next door cell contains the word "Yes"  (or the  ✔ character).

Thanx

J

yogi_Calculation Cell Referencing Across Multiple Tabs In A Spreadsheet

Google Spreadsheet   Post  #2345

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

question by: Amanda B ..

https://productforums.google.com/forum/#!topic/docs/homnp6tCgLE;context-place=mydiscussions

Calculation referencing multiple cells and across tabs
I have a spreadsheet with multiple tabs for inputs: Money we have received (Budget), Money we have spent (Actual Spent), and Balance (where I want to calculate).  All data is tied to a client name in column A.

What I want to do is be able to have a roll-up sheet (Balance) with a formula that will reference the input in Column A (client name) and in Row 1 (month/year) and calculate the difference between those values in the other tabs.  This would be used in cell H2 and copied down the line.

For example the sample client I have entered has a budget for each month in the Budget tab.

Actual spends are entered in the next tab.

in Balance, I want to calculate the difference between Budget and Actual as it relates to the client in column A, and if possible, reference Row 1 so that it will just look up those values so I don't have to mess with it down the road as long as the naming structure is consistent into 2018, 2019, and so on as we add dates down the line.

Here is the sample data I've entered into a sheet to share: 

https://docs.google.com/spreadsheets/d/10CW4mlSV9DkUtu_4orz3To6ZvHd83BnN5fvgwWetp_A/edit?usp=sharing

Any help is appreciated!  I've been mapping out how to make this work and keep running into a wall.

Thanks,

Amanda