+ Reply to Thread
Results 1 to 9 of 9

Using Cells With Formulas as Source Data for a Chart

  1. #1
    Registered User
    Join Date
    01-05-2023
    Location
    Ireland
    MS-Off Ver
    Office 97
    Posts
    4

    Using Cells With Formulas as Source Data for a Chart

    I use Excel very infrequently and every time I do, I have to relearn how to do things that are anything but basic. I'm trying to graph some data on a line chart and have used a column of dates for the x-axis and four other columns to generate the line graphs. Two of the columns produce graphs, but the other two columns couldn't be graphed because the source columns contained values with $ symbols at the start and sourced from a CSV file. I guess these are numbers and the graph is expecting values, but I read that Excel can do a conversion without using the VALUE function? I used formulas to strip out the dollar symbol from the data in the two columns and produce two new columns. Then I used these new columns as source data for the graph, but it still doesn't work.

    This is probably a simple question easily answered and I guess I have to somehow do something to the formulas to make the graph accept the values in the cells rather than the formulas.
    Last edited by Eugbug; 01-05-2023 at 07:43 PM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Using Cells With Formulas as Source Data for a Chart

    replace $ with nothing then use text to columns to change these "numbers" to numbers

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Using Cells With Formulas as Source Data for a Chart

    It is difficult to say without a sample of the file, but I would guess that something about these particular values is causing them to be seen as text, and charts will treat text as the value 0. I would test the suspect data to see if Excel sees them as number or text (ISNUMBER() and ISTEXT() functions can be useful here). If it turns out these are really text and not numbers, then review strategies for converting numbers stored as text to numbers: https://support.microsoft.com/en-us/...rs=en-us&ad=us

    In a case where numbers with an unrecognized currency symbols are being seen as text, and I wanted to use a text function (like RIGHT()) to extract the numeric portion, I would nest my text function inside of a VALUE() function [=VALUE(RIGHT(text,LEN(text)-1)) or perform some meaningless math operation on them (like adding 0 =RIGHT(text,LEN(text)-1))+0)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    01-05-2023
    Location
    Ireland
    MS-Off Ver
    Office 97
    Posts
    4

    Re: Using Cells With Formulas as Source Data for a Chart

    I'm using RIGHT(D2,LEN(D2)-1) and pasting that formula down a column to strip the "$" sign. I did try nesting this inside a VALUE function, but it didn't work, but I may have done it incorrectly, I'll try again.
    Can I determine by observation of a cell's contents whether it's a value or text?

  5. #5
    Registered User
    Join Date
    01-05-2023
    Location
    Ireland
    MS-Off Ver
    Office 97
    Posts
    4

    Re: Using Cells With Formulas as Source Data for a Chart

    OK, this seems to be working now. I had only nested the RIGHT function inside the VALUE function for one cell instead of my four test points so that's what was preventing anything being graphed, corresponding to that column.
    Is there any way I can strip the dollar signs from a column and replace the column, without generating a new auxiliary column that I use as data for the graph?

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using Cells With Formulas as Source Data for a Chart

    A number formatted as text will be left aligned. A true numeric will be right aligned. Using Text functions like RIGHT will return a text value.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  7. #7
    Registered User
    Join Date
    01-05-2023
    Location
    Ireland
    MS-Off Ver
    Office 97
    Posts
    4

    Re: Using Cells With Formulas as Source Data for a Chart

    Yes, they were left aligned when I imported the values from a CSV file.
    Anyway thanks TMS and also Sandy666 and MrShorty for the replies!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using Cells With Formulas as Source Data for a Chart

    You're welcome. Thanks for the rep.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Using Cells With Formulas as Source Data for a Chart

    My pleasure

+ 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. Excel chart with multi level category label issue delinking source data from chart
    By babu324 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-19-2017, 02:58 AM
  2. Replies: 3
    Last Post: 05-05-2015, 12:43 PM
  3. Chart not updated until chart sheet opened following source data update
    By untilyesterday in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-24-2013, 12:27 PM
  4. [SOLVED] Macro to change new chart's source data to reference cells.
    By jrod20 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2012, 11:45 AM
  5. Using combo boxes to select sheet and cells for chart source data
    By Roknroll in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-27-2012, 01:56 PM
  6. How do you have one source data in a chart flow into a 2nd source of data?
    By Disintegrate in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-06-2009, 09:26 AM
  7. Tying Chart Bars to specific cells in source data
    By LMclaughlin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-06-2007, 11:29 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