Average spend per head spreadsheet & zero values

I am trying to create a spreadsheet that shows the average spend per head for waiter/waitress in a restaurant. I would also like to rank the results and use the spreadsheet as a incentive motivator. The sheet needs to run over the course of a month broken down into weeks. My first problem is - if someone doesn't work that work I can't leave an empty row and this seems to alter the calculation. I use Average function and this didn't work. I have now used formula in cells I - K but again this is affected by the zeros or blank cells. Second problem is I cant get the league position to show in the smaller table on the right unless I have data in all the relevant cells. Can someone please offer some advice. I have looked at many posts and tried many things but as yet had no success.

Many thanks

farlmrk

Use

in J4

=IFERROR(AVERAGEIF(B4:H4,">0",B4:H4),0)

Monthly Total

in n4

=SUMIF(A:A,M4,I:I)

John,

thank you very much. That is so helpful.

Regards

Mark

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.