How to fill multiple null cells in order to calculate average?

Question

I have this example data, the excel has some description text inside, so the auto-header might be wrong. How to clean up?

And there are multiple multiple empty cells, how can I calculate the average working hours per departments?

Thank you so much for your solutions!!

DenisZ · Answer

Please find attached workflow

Do mark it as solved if it solved your issue.

Department.yxmd

flying008 · Answer

Hi, @jessiedengjie

this way for you :

1- Multi-Row Formula:

iif(isempty([Department]),[Row-1:Department],[Department])

2- Summarize:  Group By [Department] , Average of [Hour Work]