SOLVED

Using averageif to find average number of days employed in 2020 and 2021

Copper Contributor

Hi, everyone, on the far right of the excel worksheet i attached below, I'm trying to find the average number of days for nurses who's term date was in 2020, and also trying to find the average for nurses whose term date was in 2021. I found a way to use averageif to find the 2020 average if you look on cell N2, however I'm trying to use that same type of formula in cell O2 and it's giving me a division error.

 

For the criteria in the averageif functions, I used < 1/1/2021 to average only the length of days stayed for people who termed in 2020. Likewise, I used > 12/31/2020 to average only the length of days stayed for people who termed in 2021. I attached the file below, thanks for your help in advance.

2 Replies
best response confirmed by kenmustafa9 (Copper Contributor)
Solution

@kenmustafa9 

The values in columns F and G are not real dates, but text values. To correct this:

 

Select column F.

On the Data tab of the ribbon, click Text to Columns.

In Step 1 of the Convert Text to Columns Wizard, click Next >.

In Step 2, do the same.

In Step 3, select MDY from the drop-down next to Date.

Click Finish.

 

Do the same for column G.

 

Change the formula in N2 to

=AVERAGEIF(G2:G247,"<"&N4,H2:H247)

and that in O2 to

=AVERAGEIF(G2:G247,">"&N5,H2:H247)

1 best response

Accepted Solutions
best response confirmed by kenmustafa9 (Copper Contributor)
Solution

@kenmustafa9 

The values in columns F and G are not real dates, but text values. To correct this:

 

Select column F.

On the Data tab of the ribbon, click Text to Columns.

In Step 1 of the Convert Text to Columns Wizard, click Next >.

In Step 2, do the same.

In Step 3, select MDY from the drop-down next to Date.

Click Finish.

 

Do the same for column G.

 

Change the formula in N2 to

=AVERAGEIF(G2:G247,"<"&N4,H2:H247)

and that in O2 to

=AVERAGEIF(G2:G247,">"&N5,H2:H247)

View solution in original post