+ Reply to Thread
Results 1 to 4 of 4

Best way to create a chart to help identify patterns between spend and demographics

  1. #1
    Registered User
    Join Date
    03-26-2019
    Location
    Los Angeles
    MS-Off Ver
    16.15
    Posts
    2

    Best way to create a chart to help identify patterns between spend and demographics

    Hello,

    I have been trying to follow tutorials, forums and such to display the data I have but haven't gotten anywhere and wondered if anyone here could offer some help and guidance.

    I have one table with a mixed bag of data (amount spent, categories of items purchased, age ranges, residential areas).

    My goal is to find a chart that will help identify patterns between spend and demographics. I thought perhaps the bar chart with the line chart would be ideal, but I've tried creating this chart and it just becomes a jumbled mess.

    In an ideal world it would be easy to see for example age range 21-30 spend more on sky diving, 50-65 spend more on dining, those based in usa spend more than those from uk and so forth.

    I've been at this for so long I'm not entirely sure if it's possible to show all of that mixed data and have it easy to understand.

    Any help, pointers would really be appreciated.

    Administrative Note:

    Next time you post, please take a few minutes to read our forum rules about creating a proper thread title. Rule #1.
    Last edited by jeffreybrown; 03-26-2019 at 07:58 PM. Reason: Updated thread title!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,363

    Re: Best way to create a chart to help identify patterns between spend and demographics

    It's kind of a broad question without much specific, so I'm not sure what you need help with. Disclaimer -- I am not that kind of data analyst, so I don't know exactly how people in the field perform and visualize these analyses. But I do see a lot of news articles and such based on analyzing spending patterns by age cohorts and such, so I know there are a lot of people doing this kind of analysis.

    My expectation for something like this:

    1) Hopefully you have a well designed database for the raw data. It seems that a lot of database analysis questions we get here really reduce down to getting the source database well designed. It seems (to this non-database-analyst) that database analysis is easier when the database is well designed.
    2) Summarize the data. In Excel, I expect this might involve pivot tables, SUMIFS(), COUNTIFS(), or any number of other possible functions.
    3) For the chart, others presenting spending analysis by age cohort seem to like bar/column charts where age cohort is along the "x" axis and spending is along the "y" axis. Your question seems focused on the chart creation in Excel. If I assume that you already have parts 1 and 2 done, then creating the chart should mostly be about arranging the chart data in the spreadsheet (age cohort and categories in a column or two with spending/other analysis in an adjacent column), creating the chart should be as simple as selecting the source data and inserting the chart and then formatting.

    Is there a specific part of creating a chart in Excel that you have questions about?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    03-26-2019
    Location
    Los Angeles
    MS-Off Ver
    16.15
    Posts
    2

    Re: Best way to create a chart to help identify patterns between spend and demographics

    Hi,

    Thank you for your response, I think perhaps the issue might lie in my data.

    I'm new to this and unsure if using SUM would be ideal. I'll try to explain:
    Each week new data is collected (amount spent, demographics, dining bookings and similar)and the goal is to cross refernce to see why some weeks have more spending
    or bookings than others, is it based on age, people from certain countries, time of year, etc.

    If I were to sum the ages from all the various weeks I'm not sure how I could use the data in this way, but I may just not be understanding how I would total the values.

    K

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,363

    Re: Best way to create a chart to help identify patterns between spend and demographics

    It seems you are stuck more in step 2 figuring out how to analyze and summarize the data. It is difficult to give specific suggestions with knowing the specifics of the problem -- what data you have, how it is arranged, and so on.

    I put "how to do cohort analysis Excel" and "how to do demographic analysis Excel" into my favorite search engine and got several hits. A few highlights:
    A nice video focused on demographic analysis using pivot tables: https://www.youtube.com/watch?v=ftOMqX4L3SQ
    I nice overview of cohort analysis https://blog.usejournal.com/how-to-p...i=7c75f5d6910e
    This ends up with something like a heat map: https://www.projectbi.net/how-to-run...excel-tableau/

    tutorials like those should help you see the skills/tools/functions/utilities that you might use in your analysis. From there its a matter of taking the data you have and figuring out how you want to use those tools to explore your data. I'm not aware of any "plug and play" approaches to this. You will need to first learn how to use the tools, then use them to explore the data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] hello, please identify in each vertical position the three patterns always the last * draw
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-13-2018, 10:27 AM
  2. [SOLVED] Identify letter patterns in matrix rows and columns
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-23-2018, 10:09 AM
  3. How to make cumulative spend chart from pivot
    By Bglamb in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 01-08-2017, 11:42 PM
  4. Replies: 1
    Last Post: 04-25-2012, 01:17 PM
  5. How to Identify patterns in worksheet?
    By TheGovernor11 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-03-2011, 04:29 AM
  6. How do I estimate the year spend if spend is $26000 as of May?
    By Felicia Pickett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-19-2006, 11:15 PM
  7. [SOLVED] How do i get a chart showing the demographics of a country?
    By april2774 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-31-2006, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1