remaining average

**fewchurpro** · 01-13-2010, 12:40 AM

Hello, I have an interesting question and I have racked my brain on it and am wondering if any of you know a solution.

I work in a resteraunt and we track times. So I have a seven day average, where everyday they enter what their times are and it shows what the average is for the week. This part works. However, I would like to add a cell that that shows them what they would need to average to hit the goal.

So.......

If say you have the following

Mon, Tues, Wed....etc Average
3:49, 3:50, 3:55...etc 3:52

Now say the goal is supposed to be 3:50, how would I be able to add a cell that figures out and divides out for the rest of the days of the week what it would take to bring that average down. IE if say in the above, it may say we need to average 3:48 the next 4 days, and each day update as you add in the next days actual time.

Sorry if this is confusing, hard to explain it, but anyone that could help, I would very much appreciate it.

**darkyam** · 01-13-2010, 12:48 AM

If the times are in A2:G2, with the average goal in H2, try =(H2*7-SUM(A2:G2))/(7-COUNT(A2:G2))

**fewchurpro** · 01-13-2010, 01:56 AM

wow, it worked well. Only one issue though. When I tested it, it was working well until I enter the last day then I get #DIV/0! any ideas? works thru 6 of the days, just not the 7th

**DonkeyOte** · 01-13-2010, 04:31 AM

If you have 7 values then there should be no result - or at least it should be 0

If using XL2007

=IFERROR(existing formula,0)

If using pre XL2007

=IF(COUNT(A2:G2)=7,0,existing formula)