Wednesday, November 28, 2018

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

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?


Tuesday, November 27, 2018

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

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

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

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)))


Saturday, November 24, 2018

yogi_Multi-Conditional Count Computation

Google Spreadsheet   Post  #2551

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

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, 2018WENDY
Saturday, November 3, 2018RUMI
Monday, November 5, 2018RHIAN
Wednesday, November 7, 2018MICHELLE
Friday, November 9, 2018MICHELLE
Saturday, November 10, 2018WENDY
Monday, November 12, 2018RHIAN
Wednesday, November 14, 2018RUMI
Friday, November 16, 2018MICHELLE
Saturday, November 17, 2018WENDY
Monday, November 19, 2018RUMI
Wednesday, November 21, 2018RHIAN
Friday, November 23, 2018MICHELLE
Saturday, November 24, 2018WENDY
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

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:


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



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

Thursday, November 22, 2018

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

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