Sunday, October 14, 2018

yogi_Compute Years Months Days Since Member Join Date 1

Google Spreadsheet   Post  #2523

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-14-2018
count from date - teammember since date
Hello.

I use Google Sheets for my team.
There is a list of all teammembers.
I want to make that:

User1   |    joined on 10-09-2018   |   teammember since X years, X months, X days 

How can I make the "teammember since"-feature?
With =DATEIF it doesnt work.
sry for my bad English.

Thank you.

Sunday, October 7, 2018

yogi_Compute Years Months Days Since Member Join Date

Google Spreadsheet   Post  #2522

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-07-2018
count from date - teammember since date
Hello.

I use Google Sheets for my team.
There is a list of all teammembers.
I want to make that:

User1   |    joined on 10-09-2018   |   teammember since X years, X months, X days 

How can I make the "teammember since"-feature?
With =DATEIF it doesnt work.
sry for my bad English.

Thank you.

Thursday, October 4, 2018

yogi_Return a value from a cell in 1 column only if 2 other cells are consecutively 0 then 1

Google Spreadsheet   Post  #2521

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-04-2018
Return a value from a cell in 1 column only if 2 other cells are consecutively 0 then 1
I have a column (let's call it column E) where each cell in the column is a different time (i.e. 10:44:00 AM, 10:46:00 AM, etc...). I have another column (let's call it column F) where each cell in the column is either a 1 or a 0. What I want to do is get a return from column E only if 2 consecutive cells in column F are specifically a 0 and then a 1. For example:

Times.png

F3 & F4 would give me E4
F15 & F16 would give me E16
Those would be the only values that I would get back from the columns provided in the above screenshot. Thanks in advance!

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!