Hello all
First time posting on a forum like this, but this has me stumped so I'm hoping someone out there can help.
I have designed a spreadsheet which calculates averages for various metrics to evaluate an employee's performance against a number of KPIs entered onto it. The simplest table has 5 headings 'Date, KPI#1, Score, KPI#2, Score' where 'Score' is a number allocated to how well the KPI was acheived in each case from 1-10, or if there is no data " ". In another cell the average of the data in the two 'Score' columns is taken to give an overall average of how that individual is performing against all of their KPIs (some may only have two and others may have four or more). I now have data on there from the last two years. I have been asked to come up with a way to add another cell underneath the 'overall average' which would only take an average of data that is within the last year - I'm thinking I use the TODAY function and then that result-365 and use greater than and less than...but I can't get that to work.
Any ideas?? I hope I've explained myself properly, but please feel free to ask me questions to clarify anything that isn't clear!
Thanks in advance!
First time posting on a forum like this, but this has me stumped so I'm hoping someone out there can help.
I have designed a spreadsheet which calculates averages for various metrics to evaluate an employee's performance against a number of KPIs entered onto it. The simplest table has 5 headings 'Date, KPI#1, Score, KPI#2, Score' where 'Score' is a number allocated to how well the KPI was acheived in each case from 1-10, or if there is no data " ". In another cell the average of the data in the two 'Score' columns is taken to give an overall average of how that individual is performing against all of their KPIs (some may only have two and others may have four or more). I now have data on there from the last two years. I have been asked to come up with a way to add another cell underneath the 'overall average' which would only take an average of data that is within the last year - I'm thinking I use the TODAY function and then that result-365 and use greater than and less than...but I can't get that to work.
Any ideas?? I hope I've explained myself properly, but please feel free to ask me questions to clarify anything that isn't clear!
Thanks in advance!