# Dynamic-Excel-Range-Chart-Statistic-Completed

Document Sample

```					brettweese.com/excel-screencasts - Dynamic Excel (Range, Chart, S
Month                Sales
January 2011    \$   78,000.00   Curent Month:                     November =TODAY()
February 2011    \$   75,000.00   Current Month Forecast:         \$ 78,166.67 =Average(Offset(B2,Rows(Sales)-5
March 2011     \$   80,000.00
April 2011   \$   68,000.00
May 2011     \$   72,000.00   Dynamic Range
June 2011    \$   74,000.00                            10 =COUNTA(Data!\$B\$3:\$B\$5000))
July 2011   \$   78,000.00   Define Dynamic Range As:    =OFFSET(Data!\$B\$2,1,0,COUNTA(Data!\$B\$3:\$B\$
August 2011    \$   83,000.00
September 2011     \$   92,000.00
October 2011    \$   70,000.00   Dynamic Statistic
10 =Rows(Sales)
\$                   78,166.67 =Average(Offset(B2,Rows(Sales)-5,0):Offset(B2,R

Formula Syntax for this Lesson:
COUNTA(value1, [value2], ...)
OFFSET(reference, rows, cols, [height], [width])
ROWS(array)
TEXT(value, format_text)
ROUND(number, num_digits)

Hot Keys for this lesson
Alt-i-n-d (Name Manager Box)
cel (Range, Chart, Statistic)

=Average(Offset(B2,Rows(Sales)-5,0):Offset(B2,Rows(Sales),0))

a!\$B\$3:\$B\$5000))
!\$B\$2,1,0,COUNTA(Data!\$B\$3:\$B\$5000))

et(B2,Rows(Sales)-5,0):Offset(B2,Rows(Sales),0))
nge, Chart, Statistic)

Offset(B2,Rows(Sales)-5,0):Offset(B2,Rows(Sales),0))

OUNTA(Data!\$B\$3:\$B\$5000))

(Sales)-5,0):Offset(B2,Rows(Sales),0))
November Sales Forecast   =TEXT(CurrentMonth, "MMMM")& " Sales Forecast"

78,000                 =Round(CurrentMonthForecast, -3)

\$100,000.00
\$90,000.00
\$80,000.00
\$70,000.00
\$60,000.00
\$50,000.00
\$40,000.00
\$30,000.00
\$20,000.00
\$10,000.00
\$-

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 3 posted: 10/26/2011 language: English pages: 6