Conditional Average Formula Related Help

[xlmaniac]

Dear All,
I am trying to calculate the daily sales average on a dynamic basis.
The following is the sample of my data-set.
I would like to calculate the average of any month on a dynamic basis. In my data-set below, the days are pre-entered before hand. Every day basis the sale will be entered in the Sales Qty column(E) and the formula needs to add the total sales on MTD basis and then divide the total sales qty for the month by the no of days till that day.
To further explain ,till date the first 4 days of sales for March is entered and the formula needs to calculate the total sales for the first 4 days and divide by 4 to derive the average/day till 4^th March.
Similarly when the 5^th March’s sales will be filled in the formula need to aggregate the total sales from 1^st March-5^th march and then divide the same by 5 to derive the average/day till 5^th march and so on.
I have tried with one formula but it is yielding incorrect result.
I have also indicated the desired result along with the present result.
Could some body help me with the correct formula pls?
Regards
Sheet1

*	B	C	D	E
*
*
*
*	*	*	*
*	*
*	*
*	*
*	*
*	*
*	*
*	*
*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 169px;"><col style="width: 191px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Average/Day(With Formula)[/TD]
[TD="bgcolor: #FFFF00, align: center"]Average/Day(Desired Result)[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]February[/TD]
[TD="align: center"]41[/TD]
[TD="bgcolor: #FFFF00, align: center"]51[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]March[/TD]
[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFF00, align: center"]59[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Invoice No[/TD]
[TD="align: center"]Sales Qty[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]24-Feb[/TD]
[TD="align: center"]Sunday[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]25-Feb[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]35[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]26-Feb[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]42[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]27-Feb[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]55[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]28-Feb[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]71[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: center"]01-Mar[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]60[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: center"]02-Mar[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]32[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: center"]03-Mar[/TD]
[TD="align: center"]Sunday[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: center"]04-Mar[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]86[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: center"]05-Mar[/TD]
[TD="align: center"]Tuesday[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: center"]06-Mar[/TD]
[TD="align: center"]Wednesday[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: center"]07-Mar[/TD]
[TD="align: center"]Thursday[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: center"]08-Mar[/TD]
[TD="align: center"]Friday[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: center"]09-Mar[/TD]
[TD="align: center"]Saturday[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="align: center"]10-Mar[/TD]
[TD="align: center"]Sunday[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 {=AVERAGE(IF(TEXT($B$6:$B$20,"MMMM")=B2,$E$6:$E$20))} C3 {=AVERAGE(IF(TEXT($B$6:$B$20,"MMMM")=B3,$E$6:$E$20))} C6 =TEXT(B6,"DDDD") C7 =TEXT(B7,"DDDD") C8 =TEXT(B8,"DDDD") C9 =TEXT(B9,"DDDD") C10 =TEXT(B10,"DDDD") C11 =TEXT(B11,"DDDD") C12 =TEXT(B12,"DDDD") C13 =TEXT(B13,"DDDD") C14 =TEXT(B14,"DDDD") C15 =TEXT(B15,"DDDD") C16 =TEXT(B16,"DDDD") C17 =TEXT(B17,"DDDD") C18 =TEXT(B18,"DDDD") C19 =TEXT(B19,"DDDD") C20 =TEXT(B20,"DDDD") <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[jai9]

Hi,

For the March data,

Try,

=SUM(F11:OFFSET(F11,COUNT(F11:F20),0))/COUNT(F11:F20)

Where F11 has sales qty for March 1.

Change F20 to the cell which has data for March 31st.

Jai

 

[xlmaniac]

Hi,

For the March data,

Try,

=SUM(F11:OFFSET(F11,COUNT(F11:F20),0))/COUNT(F11:F20)

Where F11 has sales qty for March 1.

Change F20 to the cell which has data for March 31st.

Jai

Hi Jai,
Thamk you so much for your help.
Really appreciate the same.

Thanks