+ Reply to Thread
Results 1 to 3 of 3

Creating difficult chart based on cell colour with 9 years of weekly data

  1. #1
    Registered User
    Join Date
    10-07-2022
    Location
    London, England
    MS-Off Ver
    16.65
    Posts
    7

    Question Creating difficult chart based on cell colour with 9 years of weekly data

    Backstory
    I am a big fan of data and self improvement. Every week for the last 9 years I have tracked my performance in 4 different areas of my life. Those areas are;
    • Fitness
    • Life
    • Work
    • Finances

    Each week I allocate a colour (fill) based on my performance.
    • Complete = green
    • Majority Complete = close to dark green 15 (#006600)
    • Majority Not Complete = Orange
    • Not Complete = Red
    • N/A (Meaning no goals set, holiday, illness, etc) = Blue

    The colour allocated to each cell is based on separate spreadsheets in which I track my goals for the week and they have the same colour scoring system for each goal.

    I would like to plot the last 9 years of weekly data on a line chart to find trends and plan for 2024. For example; which months I see a dip in performance in a certain area, and which areas impact other areas.

    The X axis should be weeks (1 - 52). (I would also settle for a monthly X axis considering the amount of data).
    The Y Axis should be the performance based on the colour scale (I guess).
    I would like to overlay the years on top of each other and have 1 line representing each year.

    I also have notes on each week which I would like to plot on the chart where relevant, (if that is possible).

    I am concerned that the current format of my spreadsheet is not conducive to build a chart from. I feel like each colour would need a number allocated to it in order to plot on a chart. But honestly, I don't even know where to start.

    I attached a sample below. I am happy to share a link to the Googlesheets where this lives, or an XLS download of the full sheet, but since I am a newer member I cannot post the link here.

    The notes are often personal so I have removed them but the column is still there.

    p.s this might be impossible, but I though it would be worth posting
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,162

    Re: Creating difficult chart based on cell colour with 9 years of weekly data

    As far as Excel is concerned, you have no data.
    Quote Originally Posted by Formulawizz View Post
    I feel like each colour would need a number allocated to it in order to plot on a chart.
    This is the key, right here. A design where you use color to be the data, rather than using color to enhance data presentation, is the problem. Excel (I don't know about Google Sheets) has no built-in function to return the fill color of a cell.

    If I were designing this from scratch I would use numbers to indicate status, and conditional formatting to shows colors based on the status numbers.

    You have two options (that I can think of):

    1. Go through your 9 years of data and manually assign a number to each of your five status values. This is a bit tedious because your layout is spread across two dimensions, i.e., you cannot simply use Autofilter to filter on a color for a column or two. But you can, for example, type a 1 into cell, then copy, then select all of the light green cells, then click Paste Values to do them all in one go.

    2. Use VBA to assign the numbers as above.




    P.S. New members can't post a hot link that people can click on, but you can post a link without the https:// prefix.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-07-2022
    Location
    London, England
    MS-Off Ver
    16.65
    Posts
    7

    Re: Creating difficult chart based on cell colour with 9 years of weekly data

    I'm going to give this a shot and report back. Thank you for the helpful tips!

+ 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. Replies: 1
    Last Post: 10-04-2019, 04:30 AM
  2. Creating a spreadsheet that colour codes the cell based on a formula
    By Newbuilder in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-20-2019, 07:59 AM
  3. Chart colour based on cell text
    By pauldaddyadams in forum Excel General
    Replies: 3
    Last Post: 03-02-2017, 05:39 PM
  4. Creating a Weekly Candlestick chart
    By IamNotaDoctor in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-21-2013, 07:45 PM
  5. Replies: 2
    Last Post: 10-16-2012, 11:40 AM
  6. Colour change column chart based on cell colour
    By Alice21 in forum Excel General
    Replies: 11
    Last Post: 04-05-2011, 10:10 AM
  7. Creating a Difficult Chart
    By mlw in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-01-2005, 10:05 AM

Tags for this Thread

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