Forum Discussion
Using averageif to find average number of days employed in 2020 and 2021
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.
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)
3 Replies
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)
- Hey sup
- Thank you so much!
