Average Formula

R

RAMU

Active Member
Joined
Dec 11, 2009
Messages
345
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
Dear Friends,

I have been working with a workbook which has two sheets:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 68px"><COL style="WIDTH: 289px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">14-Aug-11</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD>LAST 10 DAYS AVG CONSUMPTION (QTY)</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">912.28</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD>LAST 30 DAYS AVG CONSUMPTION (VALUE)</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD>LAST 10 NOS AVG CONSUMPTION (QTY)</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">944.5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>=TODAY()</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 69px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">14-Jul-11</TD><TD style="TEXT-ALIGN: right">900</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">15-Jul-11</TD><TD style="TEXT-ALIGN: right">1050</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">16-Jul-11</TD><TD style="TEXT-ALIGN: right">880</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">17-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">18-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">19-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">20-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">21-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">22-Jul-11</TD><TD style="TEXT-ALIGN: right">880</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">23-Jul-11</TD><TD style="TEXT-ALIGN: right">1100</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">24-Jul-11</TD><TD style="TEXT-ALIGN: right">980</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">25-Jul-11</TD><TD style="TEXT-ALIGN: right">850</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">26-Jul-11</TD><TD style="TEXT-ALIGN: right">770</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">27-Jul-11</TD><TD style="TEXT-ALIGN: right">970</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">28-Jul-11</TD><TD style="TEXT-ALIGN: right">1050</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">29-Jul-11</TD><TD style="TEXT-ALIGN: right">1120</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">30-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">31-Jul-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">1-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">2-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">3-Aug-11</TD><TD style="TEXT-ALIGN: right">889</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">4-Aug-11</TD><TD style="TEXT-ALIGN: right">880</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">5-Aug-11</TD><TD style="TEXT-ALIGN: right">854</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">6-Aug-11</TD><TD style="TEXT-ALIGN: right">845</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">7-Aug-11</TD><TD style="TEXT-ALIGN: right">987</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">8-Aug-11</TD><TD style="TEXT-ALIGN: right">860</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: right">9-Aug-11</TD><TD style="TEXT-ALIGN: right">990</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: right">10-Aug-11</TD><TD style="TEXT-ALIGN: right">970</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: right">11-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: right">12-Aug-11</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD style="TEXT-ALIGN: right">13-Aug-11</TD><TD> </TD></TR></TBODY></TABLE>

Yellow highlighted figures in sheet 1 are the results.
Formula will be calculated based on figures of Sheet 2.

Conditions:
1) Cells of Column B of sheet 2 may be blank or "zero" "0" but result will be same.
2) Sheet two is a continuous process which generates everyday till last day's value. Formula will automatically calculate last 10 days & last 10 nos entries.

Please help.

Regards
Ramu
 
Sort by date Sort by votes

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
RAMU said:
Yes formula will exclude 0 if any blank cell filled up with 0 by mistake, as well as blank cells also.
Click to expand...

Control+shift+enter (CSE), not just enter:

=AVERAGE(IF(ISNUMBER(cQTY),IF(cQTY>0,IF(ROW(cQTY)>=LARGE(IF(ISNUMBER(cQTY),IF(cQTY>0,ROW(cQTY))),10),cQTY))))

If it can be guaranteed that there will never any text value in cQTY, including formula blanks (i.e., ""), then the shorter and more efficient:

=AVERAGE(IF(cQTY>0,IF(ROW(cQTY)>=LARGE(IF(cQTY>0,ROW(cQTY)),10),cQTY)))

which still needs CSE.

Note that these formulas exclude negative figures...
 
Upvote 0
Aladin Akyurek said:
Control+shift+enter (CSE), not just enter:

=AVERAGE(IF(ISNUMBER(cQTY),IF(cQTY>0,IF(ROW(cQTY)>=LARGE(IF(ISNUMBER(cQTY),IF(cQTY>0,ROW(cQTY))),10),cQTY))))

If it can be guaranteed that there will never any text value in cQTY, including formula blanks (i.e., ""), then the shorter and more efficient:

=AVERAGE(IF(cQTY>0,IF(ROW(cQTY)>=LARGE(IF(cQTY>0,ROW(cQTY)),10),cQTY)))

which still needs CSE.

Note that these formulas exclude negative figures...
Click to expand...

Dear Aladin,

Its GR8. Hats Off......

But one thing that I have still in my mind, is it possible if by mistake any value put against tomorrow's date, that also will not be counted for both the cases.
 
Upvote 0
Aladin Akyurek said:
Control+shift+enter (CSE), not just enter:

=AVERAGE(IF(ISNUMBER(cQTY),IF(cQTY>0,IF(ROW(cQTY)>=LARGE(IF(ISNUMBER(cQTY),IF(cQTY>0,ROW(cQTY))),10),cQTY))))

If it can be guaranteed that there will never any text value in cQTY, including formula blanks (i.e., ""), then the shorter and more efficient:

=AVERAGE(IF(cQTY>0,IF(ROW(cQTY)>=LARGE(IF(cQTY>0,ROW(cQTY)),10),cQTY)))

which still needs CSE.

Note that these formulas exclude negative figures...
Click to expand...

Dear Aladin,

Extremely sorry, In case of C2 also value is changing if I put 0.
I did not check it earlier but now am observing that value is changing.
 
Upvote 0
Aladin Akyurek said:
Not sure I understand. Do you mean the formula in C2 must ignore 0's?
Click to expand...

Yes Sir.

Moreover, in my workbook Date is a major factor, therefore my organisation uses TODAY() formula.

Here I am seeing that if I add any data against dates of 15th Aug or 16th Aug or 17th Aug etc it is also calculating.

Is it at all possible that if I put any value against future days since today that also be excluded ?

Regards
RAMU
 
Upvote 0
RAMU said:
Yes Sir.

Moreover, in my workbook Date is a major factor, therefore my organisation uses TODAY() formula.

Here I am seeing that if I add any data against dates of 15th Aug or 16th Aug or 17th Aug etc it is also calculating.

Is it at all possible that if I put any value against future days since today that also be excluded ?

Regards
RAMU
Click to expand...

1) Select A1 on Sheet1, housing:

=TODAY()

and name the selected cell CurrentDate.

2) Define Lrow as referring to:
Code: 
=MATCH(CurrentDate,Sheet2!$A:$A,1)

3) Define DateRange as referring to:
Code: 
=Sheet2!$A$2:INDEX(Sheet2!$A:$A,Lrow)

4) Define QtyRange as referring to:
Code: 
=Sheet2!$B$2:INDEX(Sheet2!$B:$B,Lrow)

5) In C2 on Sheet1 invoke:
Code: 
=AVERAGEIF(OFFSET(INDEX(QtyRange,ROWS(QtyRange)),0,0,-10),">0")

Note: AVERAGEIF requires Excel 2007 or later.

Otherwise:
Code: 
=SUMIF(OFFSET(INDEX(QtyRange,ROWS(QtyRange)),0,0,-10),">0")/
   COUNTIF(OFFSET(INDEX(QtyRange,ROWS(QtyRange)),0,0,-10),">0")

6) In C4 invoke:

Either...
Code: 
=AVERAGE(IF(ISNUMBER(QtyRange),IF(QtyRange>0,
   IF(ROW(DateRange)>=LARGE(IF(ISNUMBER(QtyRange),
     IF(QtyRange>0,ROW(DateRange))),10),QtyRange))))

Or...
Code: 
=AVERAGE(IF(QtyRange>0,IF(ROW(DateRange)>=
   LARGE(IF(QtyRange>0,ROW(DateRange)),10),QtyRange)))

which must be confirmed with control+shift+enter, not just enter.
 
Upvote 0
Dear friend ALADIN,

First of all I am grateful to you for helping me for a long time.

I have applied your formulas. In case of C4 both the formulas are working fine. But in case of C1 if I add a date only eg 14th August in Column A sheet 2 keeping Column B blank value is showing 917.6667 instead of 912.2857 against both the formulas.

Please look into this matter.

Regards
RAMU
 
Upvote 0
RAMU said:
Dear friend ALADIN,

First of all I am grateful to you for helping me for a long time.

I have applied your formulas. In case of C4 both the formulas are working fine. But in case of C1 if I add a date only eg 14th August in Column A sheet 2 keeping Column B blank value is showing 917.6667 instead of 912.2857 against both the formulas.

Please look into this matter.

Regards
RAMU
Click to expand...

Are you implying that we should not take the current day's record, that is, the record of today? Doing so would yield 912.2857 in C2.
 
Upvote 0
Yes, you are absolutely right. It will calculate last 10 days record since yesterday. Not today's record.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,609
Messages
6,179,882
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top