yogi_Compute Moving Average Of Scores For Successive Rounds

Google Spreadsheet   Post  #2520

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

question by: Jake Moore
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/A-R4rsu3ZsY;context-place=forum/docs

How to do a running/moving/rolling average?
Hello,

It seems like a very simple function or formula to need and use, however, I can't seem to find anywhere which explains how to do this in layman's terms.

So here's an example of some data (imagine each comma is a separate cell):

Round - 1, 2, 3, 4, 5

Score - 7, 11, 6, 2, 2

Of course, I can easily work this small sample out manually, but I need this to us on a much larger scale.

Worked out manually, the moving average for the score per round would be the following:

7, 9, 8, 6.5, 5.6

What function/formula can I use for Google Sheets to work this out automatically for me?

Your help would be much appreciated.

Thank you.

Kind regards,

Jake

yogi_From A:B Pull In D:E Data Only For Names Specified In Column C

Google Spreadsheet   Post  #2519

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

question by: Ian Banker
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/OI0qBi7G5NA;context-place=mydiscussions

Two columns. Copy every single match (inc. doubles) from two columns to a third column?

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

This is simply a trial version. In reality, I am attempting to keep track of student records of attendance by class.  In my real document, I have 2500+ students, with 400+ possible classes, just to give you an idea of scale.  The fake document I have above gives a decent idea.

I have a list of students in column A and the point they earned for something in column B.   I need to take the students that are listed in Column D and pull every single match from column A, meaning if John is listed 3 times, I need all 3 of his "points earned" and not just the first value, and put all of those names and the corresponding values into F and G.  They do not need to be sorted, as the initial 2500+ name list I run is already sorted when I begin.

Any help is super appreciated.  

yogi_ Pull Information from another Tab And Compute Win Loss Scores For Listed Teams

Google Spreadsheet   Post  #2518

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

question by: Ivan Penev
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/aTOd5fUciIo;context-place=mydiscussions

Error: An array value could not be found

Hello, everyone!

In Country List, Column B, I have names of countries. In another sheet, I want to check if the value of each row in column B is "Bulgaria", in this example.

When I do this:

=IF('Country List'!B59:B66 = "Bulgaria", "works", "doesn't work")

from Country List, it works*, but when I do it from any other sheet, I get "An array value could not be found". I'm using the exact same formula.

*When I say works, i mean doesn't return an error.

"Bulgaria" is the value of the cell B64, but it returns "doesn't work". Starting to think I'm not using the formula correctly.

I'd appreciate any kind of advice.

Thanks in advance.

yogi_For Entities In Column A Find The Latest Associated Date In Column B

Google Spreadsheet   Post  #2517

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

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

Stuck on a data analysis problem

I'm somewhere in the middle between a noob and a power user when it comes to google spreadsheet function.  I have experience with array formulas, but have met my waterloo when it comes to this problem.

I have some data that contains a "thing" (in this case a legislative bill) followed by several rows of elements related to that bill.  Among those elements are the dates of most recent actions related to the bill.

I need a formula that finds and evaluates those dates to find the most recent one.

I've tried several angles of attack on this simple problem, and am defeated.

I'd be grateful for any advice or help.

A more detailed explanation would be difficult. Real sample data and more description of the problem are here:

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

Thanks in advance!

yogi_Count Number Of Projects Completed By Month

Google Spreadsheet   Post  #2516

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

question by: CJ Martini
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/FbY3tkFtW3o;context-place=mydiscussions

Looking to calculate how many cells are found in each month.

Looking to calculate how many cells are found for each month.   I used a SUMPRODUCT formula in excel and have switched to google sheets and the formula will not work.  (different sheets but edited to code to reflect properly)

=SUMPRODUCT(--(TEXT('Live Project Queue'!K:K,"m")="8"))

Ideally, I would have a new tab for Results and can sort the completed projects as they are completed based on the month the dates are associated with. so I can then view completed projects for August, Sept, Oct, etc... then repeat for 2019 which would just be repeating the formula etc.

Any help is appreciated. Red outline is the column where I am getting the completed dates.

Screen Shot 2018-09-24 at 2.37.35 PM.png

146 KB

yogi_Pull Only Specified Columns From File flightlogs.csv

Google Spreadsheet   Post  #2515

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

question by: Gmails messages
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/mXGr3KC7u5w;context-place=mydiscussions

CSV into Spreadsheet , certain columns filter

Hi to all!

Can somebody help me. I am populating google sheet from external csv file from another website. Its wotking ok, i have implement script for auto update. But i want only certain columns from that csv file to be shown on google sheet, how can i do this. I have for expample 20 columns, but i want only 5 of them...Thank you

yogi_Pull Parts of Strings From Cells B2:B Into Cells E2:E

Google Spreadsheet   Post  #2514

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

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

REGEX wizz? ArrayFormula to strip unnecessary text at the start and end of a cell's value?

I need hep with an ArrayFormula to be used in Google Sheets. I have tried several formulas but have not had any luck getting it quite right.  

I am using a google sheet to compile data from daily workout emails I receive. I then pull data from these cells to populate an RSS feed. I want to strip some things out of a cell to clean up the data that is pulled into my RSS feed.  

Looking at B2 in the "Sample" tab, I have turned the text red that I'd like to match. The text in the cell will always follow this format with the URLs preceding that bit of text "Posted by Dan DeLomba..." I am assuming I could match on "Posted by" then remove everything preceding the word "Posted"?

Towards the bottom of B2 is the second part to be cleaned up. Again, I have turned the text red. In this case, I'd like to match "The Post" then remove "The Post" and everything following.  This would leave the middle bit of text to be pushed into my RSS feed. 

I have created a sample sheet with 2 tabs.  The Sample tab is laid out like my actual sheet.  I have a second tab with some notes.  If anyone would be able to help me, that would be great!  Here is the link to the sample sheet.

Thanks!

Note:  User Lance Jacobs (NYC)  helped with this formula in a previous post :

=Arrayformula(Trim(IF(B2:B="",,Replace(RegexReplace(B2:B,"(^.+\n+.+\n+)",""),Find("The post",RegexReplace(B2:B,"(^.+\n+.+\n+)","")),10000,))))

but it wasn't quite right as it worked for row 2 but missed some bits in the next 2 rows.  He asked that I repost.  

yogi_Split And Transpose Data In A2:C3 Per User Specification

Google Spreadsheet   Post  #2513

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

question by: Prince Neo
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Gbv-hPAvlQ8;context-place=forum/docs

How do you SPLIT and TRANSPOSE data at the same time?

Hello friendly smart people,

I am trying to split and transpose data at the same time, and was hoping someone might be able to help me out here.

Here is the spreadsheet I am currently working on: https://docs.google.com/spreadsheets/d/1yFswW4AB4YNyMr8mISqJh7P7Ym5ZaiW8mEloXzA7E4o/edit?usp=sharing

Thanks for any help

yogi_How To Allocate Tax Month Number For British Tax Year That Begins On Apr 6th

Google Spreadsheet   Post  #2512

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

question by: Russ Walker 7

Google Spreadsheet   Post  #2511

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

question by: Jo Smith9
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/MeIq1I8-QWg;context-place=forum/docs

How do I allocate a number to a month? (tax calendar run)

I'm finding this so tricky! if there are better ways to do this, I'm happy to consider it!

The Problem

The UK tax calendar runs from 6th April 2017 to 5th April 2018. It starts on the 6th of each month and ends on the 5th of the next month. TBH, I don't mind if your solution runs from 1st of the month to the end date of the month.

6th April 2017 to 5th May 2017 is Tax Month #1

6th May 2017 to 5th June 2017 is Tax Month #2

...

6th Dec 2017 to 5th Jan 2018 is Tax Month #9

...

6th Mar to 5th April 2018 is Tax month #12

I want to have a hidden column which would have the relevant tax month so that a Pivot Table could - in an ordered fashion - show the costs related to that month.

For Example

04/04/2018	£ 8.64	WholsaleRUs	Stock
04/04/2018	£ 5.50	Office Plus	Stationary
13/12/2017	£ 2.50	WholsaleRUs	Stock
13/02/2018	£ 6.98	MiniMart	Stock

So looking at the above table, the first two rows should be Tax Month #12, the third would be tax month #9 and the last would be tax month 11.

Thank you!

yogi_Tabulate Date For Specified Day of Every Month Unless That Day Falls On A Weekend

Google Spreadsheet   Post  #2511

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

question by: Russ Walker 7
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/MeIq1I8-QWg;context-place=forum/docs
How to enter a date that has to be on a weekday.

Hi, I want to enter the 6th of the month into a cell unless the 6th falls on a weekend (saturday or Sunday) then I want the date in the cell to be the Friday before the 6th. The formula I have managed so far moves the date forwards to a Monday and not back to the Friday, is anyone good with date formulas. Col I is the col with the formula and col A is just for Col I to get the initial date from.  Any help will be greatley appreciated.

yogi_Create Row By Row 'Group Membership" From Set Of 'E Mail Entries'

Google Spreadsheet   Post  #2510

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

question by: Thomas LIOT
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ki3DWMKgJrc;context-place=mydiscussions
Regextract in third column if Col 1 and 2 empty. If col 3 empty too give a message.
Hi guys, 

I can't solve this one alone. 

This data comes from a google contacts CSV, and the goal is to add contacts to groups named as the value between "@" and "." of the email address... (Franck@google.fr goes into google group, Mike@other.com goes into "other" group

Group Membership	E-mail 1 - Type	E-mail 1 - Value	E-mail 2 - Type	E-mail 2 - Value	E-mail 3 - Type	E-mail 3 - Value
EXEMPLE	WORK	work-email-example@EXEMPLE.fr	HOME	home-email-example@home.fr	OTHER	Other-email-example@other.fr

If email one doesn't exist, create group from mail-type-2

Group Membership	E-mail 1 - Type	E-mail 1 - Value	E-mail 2 - Type	E-mail 2 - Value	E-mail 3 - Type	E-mail 3 - Value
HOME			HOME	home-email-example@home.fr	OTHER	Other-email-example@other.fr

And if email two doesn't exist either, get email 3

Group Membership	E-mail 1 - Type	E-mail 1 - Value	E-mail 2 - Type	E-mail 2 - Value	E-mail 3 - Type	E-mail 3 - Value
OTHER					OTHER	Other-email-example@other.fr

From this example, I can create the two first groups "WORK" and "HOME" but I have got #N/A on the third... 

Here's the formula: 

'=ArrayFormula(IFERROR(REGEXEXTRACT(AL2:AL;"@(.*)\.");

IF(ISBLANK(AL2:AL);REGEXEXTRACT(AN2:AN;"@(.*)\.");
IF(ISBLANK(AN2:AN;REGEXEXTRACT(AP2:AP;"@(.*)\."))))))

I have tried with operators AND / OR / NOT ... with no success. Also, what I wold like to do is to use the "iferror" to get a text such as "this contact doesnt have email addresses yet" and put that into a contact note in another column.

Not sure already, but I could also consider getting each group separated by comma (ie: work,home,other) if a contact as more than one email address but that's not mandatory at all.

Thanks for your help guys, I have already learnt a lot reading subjects here.