• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Charting Help

M

markwilliams12

New Member
I am wanting to chart some data, that is pretty simple to do manually, but I'm interested in how to setup the series dynamically.


The data is 2 columns.

Column 1 - Date

Column 2 - Amount


Data is entered daily. It currently covers 6/1/2009 to today.


I would like to create series that show each year individually so that I can compare year over year.


I'm thinking this should be possible with an index/offset combo maybe, but don't know how to setup the year condition.


Any assistance is appreciated!
 
Here's the data:

6/1/2009 $7,623.21

6/2/2009 $7,619.67

6/3/2009 $7,616.75

6/4/2009 $7,531.05

6/6/2009 $7,514.32

6/7/2009 $7,514.32

6/8/2009 $7,514.32

6/9/2009 $7,511.09

6/10/2009 $7,421.73

6/11/2009 $7,368.50

6/12/2009 $8,040.21

6/13/2009 $8,034.51

6/14/2009 $8,034.51

6/15/2009 $7,284.51

6/16/2009 $7,261.75

6/17/2009 $6,954.57

6/18/2009 $12,361.46

6/19/2009 $11,860.85

6/20/2009 $11,628.79

6/21/2009 $11,625.17

6/22/2009 $11,625.17

6/23/2009 $11,730.73

6/24/2009 $10,777.49

6/25/2009 $10,210.67

6/26/2009 $10,162.17

6/27/2009 $10,158.94

6/28/2009 $10,158.94

6/29/2009 $10,158.94

6/30/2009 $11,648.60

7/1/2009 $10,434.23

7/2/2009 $10,418.23

7/3/2009 $10,415.12

7/4/2009 $10,409.27

7/5/2009 $10,409.27

7/6/2009 $10,409.27

7/7/2009 $10,406.04

7/8/2009 $10,578.32

7/9/2009 $10,532.49

7/10/2009 $11,422.93

7/11/2009 $11,408.50

7/12/2009 $11,403.18

7/13/2009 $11,353.18

7/14/2009 $11,247.64

7/15/2009 $11,572.42

7/16/2009 $11,569.19

7/17/2009 $11,316.52

7/18/2009 $11,266.52

7/19/2009 $11,266.52

7/20/2009 $11,266.52

7/21/2009 $11,266.52

7/22/2009 $11,231.52

7/23/2009 $10,631.52

7/24/2009 $11,509.06

7/25/2009 $11,495.56

7/26/2009 $11,485.92

7/27/2009 $11,464.70

7/28/2009 $11,460.40

7/29/2009 $10,018.50

7/30/2009 $9,870.04

7/31/2009 $10,060.41

8/1/2009 $10,132.44

8/2/2009 $10,132.44

8/3/2009 $10,132.44

8/4/2009 $10,129.21

8/5/2009 $10,125.98

8/6/2009 $10,123.31

8/7/2009 $10,983.62

8/8/2009 $10,974.15

8/9/2009 $10,974.15

8/10/2009 $10,997.84

8/11/2009 $10,541.81

8/12/2009 $10,379.36

8/13/2009 $10,716.13

8/14/2009 $11,844.23

8/15/2009 $11,733.69

8/16/2009 $11,733.69

8/17/2009 $10,079.31

8/18/2009 $11,248.08

8/19/2009 $11,095.60

8/20/2009 $10,345.60

8/21/2009 $11,300.40

8/22/2009 $11,286.80

8/23/2009 $11,286.80

8/24/2009 $11,286.80

8/25/2009 $11,281.44

8/26/2009 $11,323.21

8/27/2009 $11,301.99

8/28/2009 $10,803.80

8/29/2009 $10,754.07

8/30/2009 $10,754.07

8/31/2009 $13,415.49

9/1/2009 $12,203.20

9/2/2009 $12,199.97

9/3/2009 $12,196.74

9/4/2009 $12,119.17

9/5/2009 $12,033.68

9/6/2009 $12,033.68

9/7/2009 $12,033.68

9/8/2009 $12,169.55

9/9/2009 $12,169.55

9/10/2009 $12,100.42

9/11/2009 $12,050.42

9/12/2009 $12,050.42

9/13/2009 $12,045.79

9/14/2009 $11,745.79

9/15/2009 $14,179.35

9/16/2009 $14,120.20

9/17/2009 $13,859.90

9/18/2009 $13,669.94

9/19/2009 $13,666.72

9/20/2009 $13,661.24

9/21/2009 $13,490.03

9/22/2009 $13,490.03

9/23/2009 $13,467.51

9/24/2009 $13,453.20

9/25/2009 $13,449.97

9/26/2009 $13,443.20

9/27/2009 $13,443.20

9/28/2009 $13,443.20

9/29/2009 $13,314.73

9/30/2009 $15,969.71

10/1/2009 $14,705.17

10/2/2009 $14,635.00

10/3/2009 $14,631.77

10/4/2009 $14,631.77

10/5/2009 $14,631.77

10/6/2009 $14,574.19

10/7/2009 $13,918.87

10/8/2009 $13,880.29

10/9/2009 $13,843.54

10/10/2009 $13,841.39

10/11/2009 $13,841.39

10/12/2009 $13,841.39

10/13/2009 $13,806.16

10/14/2009 $13,734.02

10/15/2009 $17,280.70

10/16/2009 $17,421.49

10/17/2009 $17,419.34

10/18/2009 $17,377.91

10/19/2009 $17,127.91

10/20/2009 $16,965.25

10/21/2009 $16,909.28

10/22/2009 $16,305.32

10/23/2009 $16,305.32

10/24/2009 $16,303.17

10/25/2009 $16,303.17

10/26/2009 $16,303.17

10/27/2009 $16,303.17

10/28/2009 $16,414.75

10/29/2009 $16,390.24

10/30/2009 $17,076.48

10/31/2009 $17,026.48

11/1/2009 $17,117.41

11/2/2009 $17,117.41

11/3/2009 $17,114.18

11/4/2009 $17,104.83

11/5/2009 $16,526.60

11/6/2009 $16,465.25

11/7/2009 $16,445.25

11/8/2009 $16,445.25

11/9/2009 $16,445.25

11/10/2009 $13,410.02

11/11/2009 $13,405.73

11/12/2009 $13,352.50

11/13/2009 $15,989.98

11/14/2009 $15,937.32

11/15/2009 $15,933.51

11/16/2009 $15,963.53

11/17/2009 $15,639.84

11/18/2009 $15,634.47

11/19/2009 $15,502.59

11/20/2009 $15,499.36

11/21/2009 $15,499.36

11/22/2009 $15,499.36

11/23/2009 $15,498.28

11/24/2009 $15,495.05

11/25/2009 $14,995.05

11/26/2009 $14,990.76

11/27/2009 $14,969.56

11/28/2009 $14,916.61

11/29/2009 $14,916.61

11/30/2009 $17,536.66

12/1/2009 $16,126.54

12/2/2009 $16,172.25

12/3/2009 $16,169.02

12/4/2009 $16,169.02

12/5/2009 $16,144.73

12/6/2009 $16,143.44

12/7/2009 $16,143.44

12/8/2009 $16,140.21

12/9/2009 $16,051.41

12/10/2009 $16,018.33

12/11/2009 $15,964.04

12/12/2009 $15,885.81

12/13/2009 $15,885.81

12/14/2009 $15,752.08

12/15/2009 $18,194.52

12/16/2009 $18,189.15

12/17/2009 $17,338.07

12/18/2009 $17,099.19

12/19/2009 $17,049.19

12/20/2009 $17,049.19

12/21/2009 $16,998.57

12/22/2009 $16,997.49

12/23/2009 $16,993.20

12/24/2009 $13,972.42

12/25/2009 $14,262.42

12/26/2009 $14,262.42

12/27/2009 $14,262.42

12/28/2009 $14,262.42

12/29/2009 $14,237.99

12/30/2009 $14,234.76

12/31/2009 $15,338.47

1/1/2010 $15,431.55

1/2/2010 $15,431.55

1/3/2010 $15,431.55

1/4/2010 $15,431.55

1/5/2010 $15,453.68

1/6/2010 $15,449.37

1/7/2010 $15,445.07

1/8/2010 $15,439.37

1/9/2010 $15,436.70

1/10/2010 $15,436.70

1/11/2010 $15,362.10

1/12/2010 $15,203.26

1/13/2010 $15,126.93

1/14/2010 $14,835.16

1/15/2010 $17,387.21

1/16/2010 $17,330.20

1/17/2010 $17,330.20

1/18/2010 $17,329.12

1/19/2010 $15,971.75

1/20/2010 $16,561.75

1/21/2010 $16,532.65

1/22/2010 $13,524.50

1/23/2010 $13,521.27

1/24/2010 $13,521.27

1/25/2010 $13,656.34

1/26/2010 $13,655.26

1/27/2010 $13,722.30

1/28/2010 $13,720.70

1/29/2010 $17,029.93

1/30/2010 $17,026.70

1/31/2010 $17,119.37

2/1/2010 $15,553.24

2/2/2010 $15,449.01

2/3/2010 $15,444.39

2/4/2010 $15,444.39

2/5/2010 $14,618.53

2/6/2010 $14,618.54

2/7/2010 $14,618.54

2/8/2010 $14,767.45

2/9/2010 $14,772.32

2/10/2010 $14,595.98

2/11/2010 $13,935.68

2/12/2010 $16,129.65

2/13/2010 $16,126.42

2/14/2010 $16,126.42

2/15/2010 $16,126.42

2/16/2010 $16,071.80

2/17/2010 $16,060.84

2/18/2010 $16,050.32

2/19/2010 $15,344.45

2/20/2010 $15,341.22

2/21/2010 $15,341.22

2/22/2010 $15,341.22

2/23/2010 $15,336.92

2/24/2010 $15,332.61

2/25/2010 $16,258.88

2/26/2010 $18,899.47

2/27/2010 $18,845.55

2/28/2010 $18,929.47

3/1/2010 $17,726.68

3/2/2010 $15,710.12

3/3/2010 $15,704.74

3/4/2010 $15,703.67

3/5/2010 $15,698.30

3/6/2010 $15,694.00

3/7/2010 $15,694.00

3/8/2010 $15,505.15

3/9/2010 $15,425.85

3/10/2010 $15,558.44

3/11/2010 $15,473.21

3/12/2010 $15,298.14

3/13/2010 $15,294.91

3/14/2010 $15,294.91

3/15/2010 $17,548.52

3/16/2010 $17,545.29

3/17/2010 $16,621.12

3/18/2010 $16,620.04

3/19/2010 $16,314.94

3/20/2010 $16,314.94

3/21/2010 $16,314.94

3/22/2010 $13,428.16

3/23/2010 $13,286.84

3/24/2010 $13,251.47

3/25/2010 $13,203.27

3/26/2010 $13,200.04

3/27/2010 $13,195.74

3/28/2010 $13,195.74

3/29/2010 $13,190.89

3/30/2010 $13,108.74

3/31/2010 $15,700.99

4/1/2010 $14,288.04

4/2/2010 $14,284.81

4/3/2010 $14,284.81

4/4/2010 $14,284.81

4/5/2010 $14,284.81

4/6/2010 $14,262.65

4/7/2010 $14,262.65

4/8/2010 $13,546.74

4/9/2010 $13,544.58

4/10/2010 $13,542.42

4/11/2010 $13,542.42

4/12/2010 $13,702.25

4/13/2010 $13,522.17

4/14/2010 $13,522.17

4/15/2010 $16,191.74

4/16/2010 $15,840.98

4/17/2010 $15,835.60

4/18/2010 $15,835.60

4/19/2010 $14,935.60

4/20/2010 $14,932.37

4/21/2010 $14,926.99

4/22/2010 $14,796.21

4/23/2010 $14,171.90

4/24/2010 $14,161.46

4/25/2010 $14,161.46

4/26/2010 $14,161.46

4/27/2010 $14,158.23

4/28/2010 $14,153.93

4/29/2010 $14,167.56

4/30/2010 $14,810.91

5/1/2010 $14,878.23

5/2/2010 $14,878.23

5/3/2010 $14,872.91

5/4/2010 $14,819.68

5/5/2010 $14,818.60

5/6/2010 $14,817.52

5/7/2010 $14,114.35

5/8/2010 $14,112.19

5/9/2010 $14,112.19

5/10/2010 $14,112.19

5/11/2010 $14,030.19

5/12/2010 $13,870.18

5/13/2010 $13,863.72

5/14/2010 $16,486.84

5/15/2010 $16,446.85

5/16/2010 $16,446.85

5/17/2010 $15,422.81

5/18/2010 $15,418.50

5/19/2010 $15,396.34

5/20/2010 $15,158.57

5/21/2010 $14,942.04

5/22/2010 $14,938.81

5/23/2010 $14,935.60

5/24/2010 $14,934.52

5/25/2010 $14,931.29

5/26/2010 $14,915.91

5/27/2010 $14,915.91

5/28/2010 $17,449.71

5/29/2010 $17,443.90

5/30/2010 $17,515.77

5/31/2010 $17,515.77

6/1/2010 $15,685.77

6/2/2010 $15,677.29

6/3/2010 $15,674.44

6/4/2010 $15,669.06

6/5/2010 $15,665.18

6/6/2010 $15,665.18

6/7/2010 $15,665.18

6/8/2010 $14,965.00

6/9/2010 $14,960.71

6/10/2010 $15,029.24

6/11/2010 $14,866.83

6/12/2010 $14,862.90

6/13/2010 $14,862.90

6/14/2010 $14,861.82

6/15/2010 $17,389.71

6/16/2010 $17,386.48

6/17/2010 $15,447.05

6/18/2010 $15,442.74

6/19/2010 $15,428.95

6/20/2010 $15,421.67

6/21/2010 $15,385.63

6/22/2010 $15,139.23

6/23/2010 $15,139.23

6/24/2010 $15,139.23

6/25/2010 $15,139.23

6/26/2010 $15,137.85

6/27/2010 $15,129.12

6/28/2010 $15,129.12

6/29/2010 $14,997.77

6/30/2010 $17,464.66

7/1/2010 $15,875.93

7/2/2010 $15,870.55

7/3/2010 $15,868.94

7/4/2010 $15,854.90

7/5/2010 $15,854.90

7/6/2010 $15,853.83

7/7/2010 $15,849.50

7/8/2010 $14,494.76

7/9/2010 $14,494.76

7/10/2010 $14,493.68

7/11/2010 $14,493.68

7/12/2010 $14,451.08

7/13/2010 $14,334.57

7/14/2010 $14,304.79

7/15/2010 $16,844.76

7/16/2010 $15,676.49

7/17/2010 $15,671.63

7/18/2010 $15,671.64

7/19/2010 $14,171.64

7/20/2010 $14,317.47

7/21/2010 $14,314.22

7/22/2010 $13,984.46

7/23/2010 $13,980.14

7/24/2010 $13,976.89

7/25/2010 $13,976.89

7/26/2010 $14,001.04

7/27/2010 $13,997.79

7/28/2010 $13,993.47

7/29/2010 $13,819.63

7/30/2010 $14,833.57

7/31/2010 $14,772.16

8/1/2010 $14,843.65

8/2/2010 $14,843.65

8/3/2010 $14,840.40

8/4/2010 $14,840.40

8/5/2010 $14,836.08

8/6/2010 $14,015.50

8/7/2010 $14,015.50

8/8/2010 $14,011.87

8/9/2010 $13,348.17

8/10/2010 $13,311.42

8/11/2010 $12,995.02

8/12/2010 $12,992.85

8/13/2010 $15,603.19

8/14/2010 $15,603.19

8/15/2010 $15,603.19

8/16/2010 $15,530.09

8/17/2010 $13,977.58

8/18/2010 $13,974.35

8/19/2010 $13,972.18

8/20/2010 $13,793.94

8/21/2010 $13,793.94

8/22/2010 $13,793.94

8/23/2010 $13,817.94

8/24/2010 $13,814.69

8/25/2010 $13,814.69

8/26/2010 $14,508.49

8/27/2010 $14,236.64

8/28/2010 $14,233.39

8/29/2010 $14,233.39

8/30/2010 $14,197.52

8/31/2010 $16,850.10

9/1/2010 $15,316.62

9/2/2010 $15,310.57

9/3/2010 $15,307.32

9/4/2010 $15,232.32

9/5/2010 $15,229.09

9/6/2010 $15,229.09

9/7/2010 $15,239.47

9/8/2010 $14,767.06

9/9/2010 $14,762.74

9/10/2010 $14,681.76

9/11/2010 $14,681.76

9/12/2010 $14,677.95

9/13/2010 $14,592.08

9/14/2010 $14,576.69

9/15/2010 $17,156.73

9/16/2010 $17,153.47

9/17/2010 $15,892.50

9/18/2010 $15,887.28

9/19/2010 $15,887.28

9/20/2010 $16,398.68

9/21/2010 $16,396.51

9/22/2010 $16,116.69

9/23/2010 $16,057.21

9/24/2010 $16,047.33

9/25/2010 $16,047.33

9/26/2010 $16,047.33

9/27/2010 $16,208.54

9/28/2010 $16,071.82

9/29/2010 $16,059.02

9/30/2010 $17,859.55

10/1/2010 $16,317.33

10/2/2010 $16,313.52

10/3/2010 $16,307.64

10/4/2010 $16,310.63

10/5/2010 $16,307.38

10/6/2010 $16,254.16

10/7/2010 $16,249.84

10/8/2010 $15,968.80

10/9/2010 $15,968.80

10/10/2010 $15,968.80

10/11/2010 $15,968.80

10/12/2010 $15,929.74

10/13/2010 $15,923.58

10/14/2010 $15,919.26

10/15/2010 $18,410.75

10/16/2010 $18,409.67

10/17/2010 $18,405.86

10/18/2010 $18,388.16

10/19/2010 $18,359.91

10/20/2010 $17,399.56

10/21/2010 $17,396.31

10/22/2010 $17,163.87

10/23/2010 $17,153.81

10/24/2010 $17,150.00

10/25/2010 $17,145.13

10/26/2010 $17,141.32

10/27/2010 $16,560.06

10/28/2010 $16,553.78

10/29/2010 $20,406.38

10/30/2010 $20,406.38

10/31/2010 $20,465.78

11/1/2010 $18,680.87

11/2/2010 $18,677.62

11/3/2010 $18,674.26

11/4/2010 $18,666.69

11/5/2010 $18,415.61

11/6/2010 $18,414.53

11/7/2010 $18,414.53

11/8/2010 $17,914.55

11/9/2010 $17,911.30

11/10/2010 $17,907.73

11/11/2010 $17,904.48

11/12/2010 $17,868.16

11/13/2010 $12,857.52

11/14/2010 $12,853.71

11/15/2010 $15,474.02

11/16/2010 $15,472.02

11/17/2010 $15,257.92

11/18/2010 $15,345.42

11/19/2010 $15,343.25

11/20/2010 $15,342.17

11/21/2010 $15,342.17

11/22/2010 $14,435.76

11/23/2010 $14,432.51

11/24/2010 $14,431.43

11/25/2010 $14,427.11

11/26/2010 $14,810.86

11/27/2010 $14,810.86

11/28/2010 $14,810.86

11/29/2010 $14,707.31

11/30/2010 $17,318.38

12/1/2010 $15,742.54

12/2/2010 $15,739.29

12/3/2010 $15,739.29

12/4/2010 $15,739.29

12/5/2010 $15,739.29

12/6/2010 $15,489.29

12/7/2010 $15,411.04

12/8/2010 $14,126.11

12/9/2010 $14,034.41

12/10/2010 $14,028.68

12/11/2010 $14,022.41

12/12/2010 $14,022.41

12/13/2010 $13,990.41

12/14/2010 $16,085.33

12/15/2010 $12,406.47

12/16/2010 $12,405.39

12/17/2010 $12,348.75

12/18/2010 $12,348.75

12/19/2010 $12,348.75

12/20/2010 $11,424.15

12/21/2010 $11,421.98

12/22/2010 $12,354.32

12/23/2010 $12,355.44

12/24/2010 $12,353.72

12/25/2010 $12,353.72

12/26/2010 $12,353.72

12/27/2010 $12,353.72

12/28/2010 $12,350.47

12/29/2010 $12,334.03

12/30/2010 $13,749.77

12/31/2010 $13,319.19

1/1/2011 $13,374.51

1/2/2011 $13,374.51

1/3/2011 $13,374.51

1/4/2011 $13,684.48

1/5/2011 $13,431.23

1/6/2011 $11,387.98

1/7/2011 $10,635.34

1/8/2011 $10,630.48

1/9/2011 $10,630.49

1/10/2011 $10,654.87

1/11/2011 $14,708.74

1/12/2011 $14,525.74

1/13/2011 $14,525.74

1/14/2011 $17,098.04

1/15/2011 $17,094.79

1/16/2011 $17,094.79

1/17/2011 $17,094.79

1/18/2011 $17,077.09

1/19/2011 $17,077.09

1/20/2011 $15,177.09

1/21/2011 $12,167.18

1/22/2011 $12,167.18

1/23/2011 $12,167.18

1/24/2011 $12,177.56

1/25/2011 $12,382.10

1/26/2011 $11,499.68

1/27/2011 $10,584.43

1/28/2011 $10,530.13

1/29/2011 $10,530.13

1/30/2011 $10,530.13

1/31/2011 $13,997.82

2/1/2011 $12,453.14

2/2/2011 $12,453.14

2/3/2011 $15,535.57

2/4/2011 $15,535.57

2/5/2011 $15,494.49

2/6/2011 $15,494.49

2/7/2011 $15,171.94

2/8/2011 $14,411.50

2/9/2011 $14,406.10

2/10/2011 $14,243.05

2/11/2011 $14,207.81

2/12/2011 $14,206.73

2/13/2011 $14,206.73

2/14/2011 $14,201.84

2/15/2011 $17,839.73

2/16/2011 $17,785.98

2/17/2011 $17,443.51

2/18/2011 $17,440.26

2/19/2011 $17,440.26

2/20/2011 $17,440.26

2/21/2011 $17,433.98

2/22/2011 $16,607.12

2/23/2011 $16,601.72

2/24/2011 $16,597.19

2/25/2011 $16,596.11

2/26/2011 $16,595.03

2/27/2011 $16,591.57

2/28/2011 $19,453.26

3/1/2011 $17,894.15

3/2/2011 $17,731.19

3/3/2011 $17,648.92

3/4/2011 $17,818.27

3/5/2011 $17,811.39

3/6/2011 $17,807.58

3/7/2011 $17,557.58

3/8/2011 $14,535.25

3/9/2011 $14,532.00

3/10/2011 $14,522.00

3/11/2011 $14,486.75

3/12/2011 $14,451.03

3/13/2011 $14,443.52

3/14/2011 $15,243.08

3/15/2011 $18,097.72

3/16/2011 $17,394.47

3/17/2011 $16,782.32

3/18/2011 $16,779.07

3/19/2011 $16,775.82

3/20/2011 $16,775.82

3/21/2011 $15,907.61

3/22/2011 $15,620.81

3/23/2011 $15,618.64

3/24/2011 $15,613.24

3/25/2011 $15,607.51

3/26/2011 $15,548.65

3/27/2011 $15,548.65

3/28/2011 $15,544.78

3/29/2011 $15,823.66

3/30/2011 $15,755.44

3/31/2011 $18,488.98

4/1/2011 $16,945.11

4/2/2011 $16,904.03

4/3/2011 $16,900.22

4/4/2011 $17,079.07

4/5/2011 $16,829.07

4/6/2011 $16,827.99

4/7/2011 $16,824.74

4/8/2011 $15,390.33

4/9/2011 $15,387.08

4/10/2011 $15,387.08

4/11/2011 $15,387.08

4/12/2011 $15,230.84

4/13/2011 $15,209.89

4/14/2011 $15,205.00

4/15/2011 $19,106.24

4/16/2011 $19,059.06

4/17/2011 $19,055.17

4/18/2011 $17,195.20

4/19/2011 $17,189.32

4/20/2011 $16,493.50

4/21/2011 $16,925.78

4/22/2011 $16,647.08

4/23/2011 $16,643.83

4/24/2011 $16,643.83

4/25/2011 $16,643.83

4/26/2011 $16,643.83

4/27/2011 $16,638.10

4/28/2011 $16,150.75

4/29/2011 $17,174.49

4/30/2011 $17,174.49

5/1/2011 $17,228.92

5/2/2011 $17,228.92

5/3/2011 $17,225.67

5/4/2011 $17,219.94

5/5/2011 $16,955.79

5/6/2011 $16,329.98

5/7/2011 $16,326.73

5/8/2011 $16,326.73

5/9/2011 $16,326.73

5/10/2011 $16,406.11

5/11/2011 $16,371.94

5/12/2011 $16,302.13

5/13/2011 $19,210.48

5/14/2011 $19,205.59

5/15/2011 $19,205.59

5/16/2011 $19,154.62

5/17/2011 $18,937.93

5/18/2011 $18,763.08

5/19/2011 $18,738.08

5/20/2011 $17,619.24

5/21/2011 $17,536.11

5/22/2011 $17,536.11

5/23/2011 $17,666.77

5/24/2011 $17,659.71

5/25/2011 $17,899.90

5/26/2011 $17,821.65

5/27/2011 $17,797.89

5/28/2011 $17,797.89

5/29/2011 $17,793.71

5/30/2011 $17,793.71

5/31/2011 $21,085.71

6/1/2011 $18,542.17

6/2/2011 $18,535.29

6/3/2011 $18,532.04

6/4/2011 $18,521.98

6/5/2011 $18,521.98

6/6/2011 $18,271.98

6/7/2011 $18,147.92

6/8/2011 $16,980.14

6/9/2011 $16,977.45

6/10/2011 $16,972.80

6/11/2011 $16,972.80

6/12/2011 $16,972.80

6/13/2011 $16,997.80

6/14/2011 $16,975.73

6/15/2011 $19,696.26

6/16/2011 $19,692.08

6/17/2011 $19,063.89

6/18/2011 $19,063.89

6/19/2011 $19,063.89

6/20/2011 $18,179.24

6/21/2011 $18,172.36

6/22/2011 $17,883.37

6/23/2011 $17,879.58

6/24/2011 $17,876.33

6/25/2011 $17,362.69

6/26/2011 $17,362.69

6/27/2011 $17,390.60

6/28/2011 $17,388.60

6/29/2011 $16,344.63

6/30/2011 $19,080.65

7/1/2011 $17,503.43

7/2/2011 $17,491.43

7/3/2011 $17,491.43

7/4/2011 $17,491.43

7/5/2011 $17,241.43

7/6/2011 $17,238.18

7/7/2011 $17,231.30

7/8/2011 $16,778.55

7/9/2011 $16,766.05

7/10/2011 $16,766.05

7/11/2011 $16,766.05

7/12/2011 $16,727.17

7/13/2011 $16,802.34

7/14/2011 $16,781.39

7/15/2011 $19,016.76

7/16/2011 $19,015.68

7/17/2011 $19,015.68

7/18/2011 $19,011.31

7/19/2011 $19,353.84

7/20/2011 $18,517.27

7/21/2011 $18,517.27

7/22/2011 $18,222.16

7/23/2011 $18,185.51

7/24/2011 $18,185.51

7/25/2011 $18,185.51

7/26/2011 $18,182.00

7/27/2011 $18,175.12

7/28/2011 $18,172.95

7/29/2011 $20,679.88

7/30/2011 $20,677.85

7/31/2011 $20,734.80

8/1/2011 $11,005.39

8/2/2011 $11,005.39

8/3/2011 $10,996.21

8/4/2011 $10,989.06

8/5/2011 $10,587.98

8/6/2011 $10,393.99

8/7/2011 $10,388.06

8/8/2011 $9,869.81

8/9/2011 $9,462.56

8/10/2011 $9,755.68

8/11/2011 $9,684.41

8/12/2011 $9,664.46

8/13/2011 $8,911.21

8/14/2011 $8,911.22

8/15/2011 $11,970.67

8/16/2011 $12,232.56

8/17/2011 $11,844.51

8/18/2011 $11,837.06

8/19/2011 $11,835.98

8/20/2011 $11,830.18

8/21/2011 $11,828.09

8/22/2011 $10,759.37

8/23/2011 $10,752.98

8/24/2011 $10,752.98

8/25/2011 $10,758.98

8/26/2011 $10,753.78

8/27/2011 $10,753.78

8/28/2011 $10,753.78

8/29/2011 $10,493.12

8/30/2011 $10,445.03

8/31/2011 $13,406.75

9/1/2011 $11,862.77

9/2/2011 $11,809.59

9/3/2011 $11,793.52

9/4/2011 $11,793.52

9/5/2011 $11,796.70

9/6/2011 $11,392.45

9/7/2011 $11,389.20

9/8/2011 $10,284.43

9/9/2011 $10,283.35

9/10/2011 $10,274.92

9/11/2011 $10,274.92

9/12/2011 $10,239.65

9/13/2011 $9,985.25

9/14/2011 $9,980.93

9/15/2011 $14,411.50

9/16/2011 $13,945.83

9/17/2011 $13,945.83

9/18/2011 $13,442.83

9/19/2011 $13,404.17

9/20/2011 $12,567.60

9/21/2011 $12,566.52

9/22/2011 $12,150.35

9/23/2011 $12,150.35

9/24/2011 $12,150.35

9/25/2011 $12,144.41

9/26/2011 $12,139.60

9/27/2011 $12,139.60

9/28/2011 $11,942.84

9/29/2011 $11,925.46

9/30/2011 $13,486.63

10/1/2011 $13,541.28

10/2/2011 $13,541.28

10/3/2011 $13,541.28

10/4/2011 $13,538.03

10/5/2011 $12,933.71

10/6/2011 $12,930.46

10/7/2011 $12,595.07

10/8/2011 $12,593.99

10/9/2011 $12,593.99

10/10/2011 $12,593.99

10/11/2011 $12,559.53

10/12/2011 $12,522.33

10/13/2011 $12,501.32

10/14/2011 $15,520.40

10/15/2011 $15,514.99

10/16/2011 $15,509.64

10/17/2011 $14,994.17

10/18/2011 $15,011.08

10/19/2011 $15,006.75

10/20/2011 $14,165.43

10/21/2011 $13,906.68

10/22/2011 $13,906.68

10/23/2011 $13,879.53

10/24/2011 $13,873.59

10/25/2011 $13,868.19

10/26/2011 $13,862.99

10/27/2011 $13,795.39

10/28/2011 $13,054.22

10/29/2011 $13,048.81

10/30/2011 $13,048.81

10/31/2011 $15,842.21

11/1/2011 $14,292.38

11/2/2011 $14,289.13

11/3/2011 $13,126.12

11/4/2011 $13,072.87

11/5/2011 $13,069.62
 
Well, that's certainly a lot of data.


For simplicity, I'd recommend you make dynamic named ranges "Sales" for your original Col B and "DateRange" for Col A.


Sales=offset($b$1,0,0,counta(b:b))

DateRange=offset($a$1,0,0,counta(a:a))


Next, make a new tab. Starting in A2 on the new tab, run the dates 01Jan11, 02Jan11,...31Dec11 down column A. In B1-E1 put in 2009, 2010, 2011, and 2012. Then, copy this into B2 on the new tab:


=MAX((Sales)*(YEAR(DateRange)=B$1)*(MONTH(DateRange)=MONTH($A2))*(DAY(DateRange)=DAY($A2)))


Enter using Cntrl-Shift-Enter.


Copy the function across to E2 and then down to row 366 (31Dec11). You should have all the data in three columns, with 2012 ready to go.


Insert a line graph with col A as the X-axis and cols B-E as the Y. Format Col A to just show month-day, if needed.
 
Hi ,


Try out the following and see :


1. Create the dynamic named ranges Sales and DateRange that Mike has already suggested.


2. Create the following dynamic named ranges , as required :


a) =OFFSET(Sheet1!$B$1,MATCH(2009,YEAR(DateRange),0)-1,0,COUNTIF(DateRange,"<"&DATE(2010,1,1))) ; you can name this Year_2009


b) =OFFSET(Sheet1!$B$1,MATCH(2010,YEAR(DateRange),0)-1,0,COUNTIFS(DateRange,">="&DATE(2010,1,1),DateRange,"<"&DATE(2011,1,1))) ; you can name this Year_2010


Now , you can plot these ranges in your chart , by specifying them as WorkbookName!Year_2009 or WorkbookName!Year_2010 , where WorkbookName is the name of your workbook.


Narayan
 
@Mike86 - Thanks for the reminder to used Name Ranges. Can't believe I forgot about that option!


@NARAYANK991 - I used your part 2 Very nice formula! Works great!


Thank you both.
 
You must log in or register to reply here.
Back
Top