• 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.

Using CountA and Offset formulas for 13 month dynamic graphs

N

Nikki

New Member
Hi!


All the examples show that the data is in rows, but if the data is in columns, how does one get this to work?


I have tried the below but it doesn't work


Data in one row along columns


=OFFSET(Sheet1!$J$8,COUNTA(Sheet1!$J$8:$AG$8)*0,0,1,13)


This works well because the data is in one column but along rows


=OFFSET('Cheat Sheet '!$A$26,COUNT('Cheat Sheet '!$A$26:$A$49)-1,0,-13,1)
 
Nikki


Firstly Welcome to the Chandoo.org Forums


The format of Offset is: =Offset(Ref, Rows, Columns, [Height], [Width])


So in your first example

The Rows are offset from J8 by COUNTA(Sheet1!$J$8:$AG$8)*0 Rows

Which should be 0 (I assume there is a mistake in the typing)

The Columns are offset 0

The size is 1 Row high and 13 Columns wide


So in the second example

It probably should be something like

=OFFSET('Cheat Sheet '!$A$26,0,COUNT('Cheat Sheet '!$A$26:$A$49)-1,1,13)


Read more about Offset here: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
 
Here's a little help:

There's an extension out there called Name Manager. (http://www.jkp-ads.com/officemarketplacenm-en.asp). Amongst other things, it has an evaluate button which really helps troubleshoot wonky dynamic ranges.
 
Thanks Hui and for your help but I just can't get the formala to work. "On paper" it all looks fine but the formula doesn't seem to recognise the Offset function. The * was not a typing error - EXCEL returns an error without it?


Our Dashboards have data from Jan 2010 to Dec 2011 going accross in columns, so i want the graphs to read the latest 13 months' data.


[the second formula works perfectly but then the data is going down in rows which is not how our Dashboards are set up]
 
Hi Nikki ,


Can you post the exact formula you are using , along with the cell in which you are using this formula ?


Narayan
 
Nikki


Can you post the file or some data ?


Also make sure your using Dates as Numbers not as Text
 
Headcount January 2010 February 2010 March 2010 April 2010 May 2010

Perms 52 53 54 55

Temps 2 3 4 5


=OFFSET(Sheet1!$J$8,COUNTA(Sheet1!$J$8:$AG$8)*0,0,1,13)


Thanks All - wasn't able to paste all the data, but the range is from I8:AG10. I would like the graphs to reflect the latest 13 months of data.The COUNTA formula doesn't seem to work?
 
Hi Nikki ,


Create the following two dynamic named ranges :


1. Date_Range , referring to : =OFFSET(Sheet2!$J$8,0,COUNTA(Sheet2!$J$8:$AG$8)-13,1,13)


2. Data_Range1 , referring to : =OFFSET(Sheet2!$J$9,0,COUNTA(Sheet2!$J$9:$AG$9)-13,1,13)


Now , you can plot your chart to reflect the most recent 13 months of data.


Add more data ranges , using similar formulae , to plot any additional series.


Narayan
 
You must log in or register to reply here.
Back
Top