Need Flexible Average based on Year

[dwgnome]

Hi,

I am in need of modifying the existing average formula in H6 to allow user input as shown. That is user picks number of years to average and the starting year along with which equipment. I would like to keep the SUMIF/COUNTIF construct to avoid all errors if possible.


Baseline Comps


<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: 122px"><COL style="WIDTH: 96px"><COL style="WIDTH: 89px"><COL style="WIDTH: 97px"><COL style="WIDTH: 102px"><COL style="WIDTH: 97px"><COL style="WIDTH: 97px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 16px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: right">3</TD><TD>Choose # of consecutive years</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="BACKGROUND-COLOR: #ff99cc; TEXT-ALIGN: right">2009</TD><TD>Choose Start Year</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 24px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 14pt; FONT-STYLE: italic">Baseline by Year</TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">Distrib</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffffcc">3-yr avg:</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center">2.7</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; BACKGROUND-COLOR: #ccccff"></TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff">Market-Share</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff"></TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff"></TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff"></TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff"></TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff"></TD></TR><TR style="HEIGHT: 24px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff">Equipment</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">2006</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">2007</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">2008</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">2009</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">2010</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">2011</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: left">Store1</TD><TD style="TEXT-ALIGN: center">0.69</TD><TD style="TEXT-ALIGN: center">1.78</TD><TD style="TEXT-ALIGN: center">2.86</TD><TD style="TEXT-ALIGN: center">1.93</TD><TD style="TEXT-ALIGN: center">0.85</TD><TD style="TEXT-ALIGN: center">0.46</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: left">Store2</TD><TD style="TEXT-ALIGN: center">22.8</TD><TD style="TEXT-ALIGN: center">33.3</TD><TD style="TEXT-ALIGN: center">27.9</TD><TD style="TEXT-ALIGN: center">29.0</TD><TD style="TEXT-ALIGN: center">31.2</TD><TD style="TEXT-ALIGN: center">27.4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Store subtotals</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">23.5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">35.1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">30.8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">31.0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">32.0</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">27.8</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: left">Distrib</TD><TD style="TEXT-ALIGN: center">2.81</TD><TD style="TEXT-ALIGN: center">0.296</TD><TD style="TEXT-ALIGN: center">1.4</TD><TD style="TEXT-ALIGN: center">3.4</TD><TD>#DIV/0!</TD><TD style="TEXT-ALIGN: center">2.00</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">Total</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">26.3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">35.4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">32.2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">32.6</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">33.1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">28.5</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>H6</TD><TD>=IF(ISERROR(SUMIF(F12:H12,">0")/COUNTIF(F12:H12,">0")),"",SUMIF(F12:H12,">0")/COUNTIF(F12:H12,">0"))</TD></TR><TR><TD>C11</TD><TD>=SUMIF(C9:C10,"<10E37")</TD></TR><TR><TD>D11</TD><TD>=SUMIF(D9:D10,"<10E37")</TD></TR><TR><TD>C13</TD><TD>=SUMIF(C9:C12,"<10E37")-C11</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Excel 2003, Win XP

Thank you.

 

[Matt Rogers]

Hi.

Sorry, I don't understand your formula in H6
I think it's =AVERAGE(F12:H12)

 

[dwgnome]

Yes, it is equivalent to AVERAGE(F12:H12) but it takes into account blanks and errors. Looking to modify it to average the entire range C12:H12 but with the modifer to include only certain years as user input.

 

[dwgnome]

Okay, I came up with this formula:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

=[COLOR=navy][FONT=Verdana]SUMIF(OFFSET(INDEX(C8:H8,MATCH(C5,C8:H8,0)),4,0):OFFSET(INDEX(C8:H8,MATCH(C5,C8:H8,0)+C3-1),4,0),">0")/COUNTIF(OFFSET(INDEX(C8:H8,MATCH(C5,C8:H8,0)),4,0):OFFSET(INDEX(C8:H8,MATCH(C5,C8:H8,0)+C3-1),4,0),">0") [/FONT][/COLOR]

<o></o>
<o></o>

Just hoping someone can help direct the formula to the appropriate row based on item choice in F6. If Distrib is chosen then have formula compute average (as shown above) on row 12, if Store 1, then computation should be made on row 9.<o></o>
<o></o>
Thanks

 

[Ron Coderre]

Using your posted example,
this regular formula would return the average of the Row_12 NON-#DIV/0! cells
beginning with the referenced year and for the number of referenced cells

H6: =SUMIF(OFFSET(C12,,C5-C8,1,C3),"<>"&"#DIV/0!")/
COUNTIF(OFFSET(C12,,C5-C8,1,C3),"<>"&"#DIV/0!")

Is that something you can work with?

 

[dwgnome]

Thank you, a much more compact solution. Works for the exact example. However if with the specified range one or more of the cells are blank or has other errors such as #NAME? or TRUE or any text, it treats it as a zero and it averages the zero. Is there a work around for those cases?

 

[Ron Coderre]

Is there a workaround? Yes
But, it think it would be much wiser to adjust the formulas in the problem cells to prevent garbage from exising in your analysis range. When I was a finance manager, an analysis that had error values in it was a red flag to me.

 

[dwgnome]

Ron, yes that is always the best managment practice, and I normally do that, however, in this case and for now, I am requesting a solution as presented. Also, how can I incorporate to choose the appropriate row to act on.

 

[dwgnome]

Slight modification to Ron's formula seem to do the trick. This formula works:

 [COLOR=navy][B]=SUMIF(OFFSET(C12,,C5-C8,1,C3),">0")/COUNTIF(OFFSET(C12,,C5-C8,1,C3),">0")[/B][/COLOR]

Is there anyone that can help me figure out how to choose the correct row based on the item in F6 matching those in B9:B12?

 

[Ron Coderre]

Here you go...Try this formula:

H6: =SUMIF(OFFSET(C6,MATCH(F6,B7:B12,0),C5-C8,1,C3),">0")/
COUNTIF(OFFSET(C6,MATCH(F6,B7:B12,0),C5-C8,1,C3),">0")

It calculates on the row containing the F6 data (Store1, Distrib, etc.)

Does that help?