Calculating estimated loss

[alibahman]

Hi,

I have a list of online sales figures. for sometimes the payment system is updated or is down the sales become zero in that particular time because customer cant book online.

I want to calculate the estimated loss in that time where the payment system is down.

for example: the below is for 9 hours where 3 hours there is zero sales when the payment system was down

Column A

Hours
00
01
02
03
04
05
06
07
08
09

Column B

Sales
6
12
20
26
26
0
0
0
15
12

I hope there is a way to calculate that.

thanks

 

[CWatts]

It's sort of possible, but it would depend on how you want to estimate each interval's value.

Just using the data you have, you could use the below formula with Excel 2007+:

=COUNTIF(B2:B11,0)*AVERAGEIF(B2:B11,"<>"&0)

The first part will count the number of zero intervals, and the second will determine the average of all intervals that aren't zero. Multiplying them together will give you an estimated total loss.

Of course, if you legitimately have no sales during an interval, that will throw it off, but if you're just kind of looking for a guesstimate that will hopefully work for you.