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.

yogi_Formulate Price Computation For Work Given House_Number Chore And Rate Per Square Meter

Google Spreadsheet   Post  #2546

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

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

Preisausrechnung

Ich möchte eine automatische Preisausrechnung erstellen. 

Die Situation ist folgende: 

Ich habe eine Auswahl für die Hausnummer (1,2,3,....) und eine Auswahl für die ausgeführte Arbeit (Boden reinige, Fenster Putzen,...).

Pro Hausnummer ist ein Wert (Bodenfläche z.B.) auf einem separaten Tabellenblatt hinterlegt. Ebenso ein Preis pro Arbeit.

Ich möcht also einrichten, dass bei Auswahl Hausnummer und Auswahl Arbeit der richtige Endpreis in dem Feld angezeigt wird.

(1 = 0.76) * (Boden reinigen = 50.00) = Endpreis

(15 = 12.3) * (Rasen mähen = 20.00) = Endpreis

Wie muss ich mir das einrichten oder welche Formel kann ich verwenden?

Bin eher ein Neuling auf dem ganzen.

Herzlichen dank für eure Hilfe

yogi_Compute Currecy Conversion Rates For Dates Specified In Column B

Google Spreadsheet   Post  #2545

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

question by: Leniel Maccaferri:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/zWcGH1taSnQ;context-place=forum/docs

How to get GoogleFinance with Index formula to work in ArrayFormula?
Hi guys,

Right now I'm trying to get automatic daily quotes for USD price for a given day.

I have this sample spreadsheet:

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

For each date I get the USD value converted to Brazilian Reais. This works great as long as I manually apply the formula in D3 to the rows below.

If I use this same formula inside ArrayFormula, just the 1st row is filled in E3.

Is there any equivalent function that can substitute INDEX?

Thanks for any input on this.

P.S.: this answer @ StackOverflow tells us that the INDEX function is one that does not support "iteration" over an array if an array is used as one of its arguments.

https://stackoverflow.com/a/16141020/114029

I tried to adapt it to my use case with the VLOOKUP alternative but I couldn't get it to work.

yogi_Extract 1st Preference Information From (row 3) 'Roster' For All Applicable Entries In Roster!B4:B

Google Spreadsheet   Post  #2544

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

question by: Cody TimQuinn:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/obbPwcB_2a4;context-place=forum/docs

Trying to lookup multiple cell variables?
I'm looking to search for someone's name and their preferred 1st role, I'm not sure if I'm explaining this well haha. 

I would be looking for their name, finding their 1st pref. and determining what role that is

Cat's 1st pref. would be ADC

yogi_For Entities In N2:N18 Compute Entries In Corresponding Rows For Matching Entries In Column C In Table COURSE_BLOCK_MAPPING!D4:X88

Google Spreadsheet   Post  #2543

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

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

Confused on the use of LOOKUP in this formula.
I do not understand why LOOKUP in this formula behaves as it does and uses the number 2 as the search term and how it properly works with the rest of the formula.

Additionally I also do not understand why I cannot properly set up this formula as an array of answers like I would expect. Any help would be appreciated.

Link to the example document:

https://docs.google.com/spreadsheets/d/1K43UUp4QVxsAn-1uswSse766y1gm-Syq2oLJyqXS2xE/edit?usp=sharing