Formula required

[RAMU]

Dear All,

I have a workbook of two sheets. First sheet Calculation Part & second sheet contains data.

In First sheet
A1 is =TODAY()
B2 is "AVG DAILY DEMAND"
B3 will calculate that "AVG DAILY DEMAND" based on sheet 2 data.

In Second sheet
Column A contains dates & Column B contains consumption.

In Sheet 1 B3 will show result last 15 avg consumption (sheet2 columnB) starting from the previous day. I mean if any cell left blank in sheet 2 Column B that will not be counted.

Please help me providing the formula.
Regards
RAMU

 

[SPACKlick]

Not sure I understtod you fully, but if you want the average to count 0's and it is always 15 cells worth of data, just use SUM(B3:B17)/15 (changing B3:B17 to be the relevant cell range)

 

[marka87uk]

You can use =AVERAGE(B3:B17).

It'll ignore blanks but use 0's within the calculation.

 

[RAMU]

Dear Friends,

No no, suppose today is 29th July, in data sheet last date was entered 28th. Now in Sheet1, B3 calculation will start from 28th & it will take last 15 entries. that means from 28th to 14th. but if there is any blank then it will go more reversely. That means from yesterday it will count 15 valid entries, not the bank cells.

I am not sure whether I can convince you all.

Regards
RAMU