yogi_Create Pivot Table For Selected Clients From Raw Data

Google Spreadsheet   Post  #2085

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-20-2016

question by: Wynne Walker:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!msg/docs/aAI6LrJ0Q88/NBmAIbqAAgAJ;context-place=topic/docs/8ogMiVvgF2I
Help with query and pivot

Well, I tried for over hour and gave up on this. I am new to query formulas and pivots.

I have a tab with the columns: Client, Date, Actual Hours. This is imported from a bunch of raw data from our time tracking software.

I have a 2nd tab with the columns: Client, Target Hours. The data in these 2 columns is manually entered.

I want a 3rd tab to have the columns: Client from tab 2, Target Hours from tab 2, Actual Hours by Month (multiple columns for each month from tab 1), Total of all months.  See below and attachment. 

Client          Target Hours           Jan    Feb    March   Apr  .....   Total

John Doe           2                         .5      1         3          2              30

Jane Doe         etc ......

On the attachment its using a pivot table, which is formatting it EXACTLY how I want BUT its not filtering out the clients we've deleted by manually entering them on tab 2. So imagine we have 100 clients but we only care about tracking our time on 50 of them. Thats what the 2nd tab is for, its a manually selected list of clients we care about and we want the 3rd tab, time by month, to only show the 50.

I tried the following query which works for one row, John Doe (A2) but I dont know how to make it continue down the rows by having A2 increment to B2, C2, etc. 

=query('RAW Data Time'!A:AA,"select AA, sum(K) where AA ='"&A2&"' group by AA pivot A")

Sorry if this is super confusing!!

Thanks!

Screenshot 2016-11-20 11.04.29.png

175 KB

Categories:ChromeAsk a "

yogi_modify entries in range A2:A matching those in D1:H1 as specified in the question by the poster

Google Spreadsheet   Post  #2084

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-20-2016

question by: BillTeach:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/8ogMiVvgF2I;context-place=mydiscussions
Regex formula to replace multiple first words with multiple substitutes

How do I copy from one sheet to another if cell string begins with an apostrophe.

I want to substitute only at the beginning of a string (followed by a space).  For example:

m-> 'm

s -> 's

d -> 'd

ll-> 'll

re -> 're

've -> /ve

or in the complete string:

m going home -> 'm going home

Something like =substitute(A1, "m", char(39)&"m") works for one substitute, but I think regexreplace will be better to replace multiple substrings and only at the beginning of the cell string.

yogi_Compute Stats For Hourly Visitor Count For WeekDays And WeekEnds

Google Spreadsheet   Post  #2083

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-12-2016

question by: Nudus:

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

Trying to pull out metrics off a visitor log.

 Hey guys!

I'm trying to figure out a way to pull average visits on an hourly base - So I can tell how many people visited on average per hour for different hours.

I have the log files that our swipe card system creates , I attempted to create a Pivot Table but I cant seem to get them to display unique only.

Ideally I'd like to be able to get statistics for weekdays / weekends separately.

File link:
https://docs.google.com/spreadsheets/d/1wzJejhfh-yL_y-rvHC_lgD8y_HR-wCZkDfwLN0d7wUw/edit?usp=sharing

Would really appreciate any help, I'm at a loss here :<

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

yogi_Compute Row By Row And Column By Column Average Of Entries Submitted via Google Form

Google Spreadsheet   Post  #2082

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-08-2016

question by: BMosty:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/PCkUjhZIZPU;context-place=forum/docs
How do I find the average of row sums when pulling data via Google Forms?

I am collecting data using Google Forms and retrieving responses in sheets.  Because each form response adds a row, I am having trouble with my formula.  

I need to find the total numerical score of each row and then find the average of these row totals.  I have established a separate sheet to run the formulas, but the combination of pulling data from a separate sheet, totaling across a row, and then averaging several totals is beyond my limited technical abilities.  

For example:https://docs.google.com/spreadsheets/d/1n2qhLu0vBQ4x-JuvUm5OAaamEitS6G4ADa36-Q2T9Uc/edit?usp=sharing

I have a sheet named "MP1 Responses."  I would like to find the total score of each row depending on the number of Google Form responses. Then, I need to find the average of row 2's sum, row 3's sum, row 4's sum, etc.  Then, I would like this average score to appear in B2 of my sheet named "Totals."

Based on my reading, it seems that I might need to use an array, but I am not sure how.  Any help would be appreciated.

-BMosty

yogi_Create Column Chart To Show Monthly Values For Various Attributes And Their Respective Totals

Google Spreadsheet   Post  #2081

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-02-2016

question by: Bronwyn Wakeman:

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

Converting to chart

How do I convert this to a chart?

Injury
July		August		September		October
with	without	with	without	with	without	with	without
7	19	6	9	6	12	7	19

yogi_Create Formula For Consecutive Dates Repeated Specified Number Of Times Leaving Specified Number Of Cells Blank

Google Spreadsheet   Post  #2080

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-31-2016

question by: Whitney Lewis:

https://productforums.google.com/forum/#!topic/docs/MIKhgFpAau4;context-place=forum/docs
How to create formulas for consecutive dates in cells?

Hi I have column "B" with date of 10/29/16 entered in cells 510 through 531.

I would like the next set of cells in column "B" 534 through 555 to read next date of 10/30/16

and then continue through the sheet.

Is this formula possible?

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

yogi_Counts The Number Of Times Each Value In B Appears In Column B for Each Group In A

Google Spreadsheet   Post  #2079

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct-30-2016

question by: michaeldon:

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

How to count number of times the value in each row occurs with arrayformula

ADD A REPLY

Hello All

If I have data like the screen shot, and I want to count the number of times each value in column B appears for a group in column A I can use the formula =countifs(A:A,A1,B:B,B1) and paste it into every cell in Column C.

But what if I want to use an arrayformula? Countifs cannot be used with arrayformula and I have found it impossible to replicate this result using IF or countif

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