Graphing the accumulation of new fields over time, please help!

[Feracon]

Hello everyone,

I have a spreadsheet which holds email addresses submitted by users who want to join a list. These sit in A1-A### on sheet 1.

On sheet two I am trying to build a graph which will show the trends of this lists growth over time.

I've been able to use the COUNTA function to measure the total non blank cells in the column and graph that over time. However using this method I wont see a change over time because as I add to the list of emails no history of previous totals is kept.

I hope this makes sense enough that someone can help me figure out a formula to accomplish what I need.

Thanks in advance for any help!

Dan

[jeffreybrown]

Hi Dan,

When you say, "growth over time", does this mean you also have a column with dates the user join's the list.

Sure would help to see a sample spreadsheet, but my initial impression is a pivot table...

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Feracon]

This is the sort of graph im after (please see attached), again the total members for each date here is added manually by checking how many rows we're down to at each date. So the first email list had 689 rows (1 new email per row in column A) the first month, 750 the next and so on.

I'd like to measure the number of non blank rows at a point in time, record it, and retain that history so that number doesn't change as more entries are added over the next month.

Attachment 255880

[Feracon]

On the graph, rows designate the list in question, columns designate date the total was recorded.

Thanks Jeffrey, I'll prepare and attach the sample sheet shortly!

[Feracon]

On the graph, rows designate the list in question, columns designate date the total was recorded.

Thanks Jeffrey, attached is the sample document! Let me know if this makes my objective more clear.

[MrShorty]

The graph itself should be easy to make, once you've got the historical data tallied up. So, as I see it, the problem is to figure out how to store the historical information/counts.

How often does this need to be done? Your sample suggests once/month. If so, and you are willing to do some of the work manually, it might be easiest to have a =COUNTA() formula for each list, then manually enter (or copy/paste special as values) these counts into the Graph tab. If you only have a few lists and only need to do it once a month, this could save a lot of programming effort.

One problem with the List tabs is that there is no historical information stored. Could you add a date/time stamp of some sort to each list? If you could add some indicator of "when was this email added," then a relatively simple =COUNTIF() function should be able to tally up the results for each month on the Graph tab.

Do either of those sound like workable solutions?

[Feracon]

It sounds like the easiest way to do this may be to just use COUNTA and manually stop the counting at the appropriate point down the list for the given date point on the graph.

[jeffreybrown]

Hi Dan,

Here is one way...

• A2:A3 need to show the exact name as the Sheet tabs
• Notice the table of data A1:D3 was converted into a table. Converted into a table to make it dynamic
• Next month after you put 9/1/2013 in E1, the table will automatically adjust to include E1:E3
• Highlight D2:D3 and drag right which will copy the formulas over to E2:E3
• Now enter your new data to the bottom of your lists in the tabs and place a date marker in column B signifying the date you received the data
• Once you enter the new data the table will automatically update with the new numbers as you already copied the formulas

Maybe some of these links might help...
http://www.techrepublic.com/blog/mso...arts-in-excel/
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/WordPress/eas...-lists-tables/