CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user10531291
user10531291

Reputation:

formula to get the average date and set a score

I am trying to create a formula from a table where there are several agents in which column A has a time stamp, I need to create a formula to get the average date of column A of each agent, get the oldest date of the column and the most recent date, and from those 3 information generate a score from 1 to 10, 10 being the average being close the the oldest date, and low score means that the average is close to the newest date.

Link to the sheet

I have a sheet 1 with all the information, and in sheet 2 is where i have a column with the agents name and another value would be the average score.

Any help will be very much appreciated.

Upvotes: 0

Views: 151

Answers (1)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(QUERY({VALUE(Sheet1!A1:A), Sheet1!B1:B}, 
 "select Col2,avg(Col1) 
  where Col2 is not null
  group by Col2
  label avg(Col1)'Average' 
  format avg(Col1) 'mm/dd/yyyy'", 1))

enter image description here

Upvotes: 1

Related Questions