1
votes

I know very little about writing Excel functions and I'm a bit stuck.
I have an overview page on an Excel doc and I am trying to display averages for each employee. The issue here is that I can't get the correct average. The average needs to:

  1. Be within a certain time period (for the week [Saturday-friday]).

  2. Match the name for the average on the data page to the name on the overview page.

  3. Have a parameter where '--' will display if a specified cell is under a certain number.

OVERVIEW!$C$2 is the cell that has the week start date and OVERVIEW!$C$3 has the week end date. So I am trying to use that in my function.

ratings data'!D:D is the page/column that has the data for what I am trying to average for the week (the 'ratings data' page has a years worth of data).

ratings data'!A:A has the dates for each piece of data I'm trying to average (This is the date that needs to be between the week start and end dates on the overview page).

So I wrote this, and I believe it's close, but I'm getting a #DIV/0! error:

=IF(OVERVIEW!$G$8>"10"(AVERAGEIFS('ratings data'!$D:$D,'ratings data'!$A:$A,">=OVERVIEW!$C$2",'ratings data'!$A:$A," <=OVERVIEW!$C$8",'ratings data'!$C:$C, $B12), "--")
1

1 Answers

2
votes

Try this version

=IF(OVERVIEW!$G$8>10,AVERAGEIFS('ratings data'!$D:$D,'ratings data'!$A:$A,">="&OVERVIEW!$C$2,'ratings data'!$A:$A,"<="&OVERVIEW!$C$8,'ratings data'!$C:$C, $B12),"--")

Numbers are not normally in quotes so I removed the quotes from 10

when you compare against a cell for a criterion in AVERAGEIFS you need to concatenate like this

">="&OVERVIEW!$C$2

Should OVERVIEW!$C$8 actually be OVERVIEW!$C$3 as your text description says?