Saturday, September 29, 2018

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

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.



Thursday, September 27, 2018

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

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:

Thanks in advance!

Tuesday, September 25, 2018

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



Monday, September 24, 2018

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

Thursday, September 20, 2018

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

Monday, September 17, 2018

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

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

Monday, September 10, 2018

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     
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.64WholsaleRUsStock
04/04/2018£ 5.50Office PlusStationary
13/12/2017£ 2.50WholsaleRUsStock
13/02/2018£ 6.98MiniMartStock

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!



Sunday, September 9, 2018

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.
Capture.JPG



Saturday, September 8, 2018

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 MembershipE-mail 1 - TypeE-mail 1 - ValueE-mail 2 - TypeE-mail 2 - ValueE-mail 3 - TypeE-mail 3 - Value
EXEMPLEWORKwork-email-example@EXEMPLE.frHOMEhome-email-example@home.frOTHEROther-email-example@other.fr

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

Group MembershipE-mail 1 - TypeE-mail 1 - ValueE-mail 2 - TypeE-mail 2 - ValueE-mail 3 - TypeE-mail 3 - Value
HOME

HOMEhome-email-example@home.frOTHEROther-email-example@other.fr

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

Group MembershipE-mail 1 - TypeE-mail 1 - ValueE-mail 2 - TypeE-mail 2 - ValueE-mail 3 - TypeE-mail 3 - Value
OTHER



OTHEROther-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.