Charting with concatenated numbers and text

I am scatter charting from columns of data (text and 
numbers) that I concatanate into a long string so that I 
can use the validate drop down list feature in Excel,  
which only supports one column. I use Mid, Left and Right 
functions to then pick the relevant charting data from the 
string.

Unfortunately although the numbers look good, Excel
does not treat them the same way that it does with 
directly entered numbers - and my charts do not display 
accurately.
Any ideas to to fix this problem or suggest a modified 
workflow. I am not an advanced user but would like to use 
the drop down list approach if possible.

0
anonymous (74722)
1/30/2004 11:16:20 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
461 Views

Similar Articles

[PageSpeed] 46

Change the text result to a number in your formula. For example:

   =RIGHT(A2,2)+0

Alan Dresch wrote:
> I am scatter charting from columns of data (text and 
> numbers) that I concatanate into a long string so that I 
> can use the validate drop down list feature in Excel,  
> which only supports one column. I use Mid, Left and Right 
> functions to then pick the relevant charting data from the 
> string.
> 
> Unfortunately although the numbers look good, Excel
> does not treat them the same way that it does with 
> directly entered numbers - and my charts do not display 
> accurately.
> Any ideas to to fix this problem or suggest a modified 
> workflow. I am not an advanced user but would like to use 
> the drop down list approach if possible.
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
1/30/2004 11:43:06 AM
Reply:

Similar Artilces:

numbering
Is there a way to format a cell so that you can put in a 1 and then the next time you print it out that number will change to 2 and so on? ...

Conditional charts
Is there a way to change the color of a chart bar based on the value of the cell that generates that particular bar? In other words, I have a chart with 15 vertical bars and I want to change the color of one or more of the bars if its value goes above 100% (but not all of the bars). JWeinberg Jon Peltier has instructions for conditional charts: http://www.peltiertech.com/Excel/Charts/format.html#CondChart Jeff wrote: > Is there a way to change the color of a chart bar based on the value of the > cell that generates that particular bar? In other words, I have a chart > wi...

excel charts #24
Is it possible to freeze a chart in excel and then add another chart with different column widths without having the changes affect the first chart? Hi, Move off the chart, press the Shift or Ctrl keys and select the chart. Then right-click it and choose Format Object On the Properties sheet select Don't move or size with cells -- Thanks, Shane Devenshire "Lorena" wrote: > Is it possible to freeze a chart in excel and then add another chart with > different column widths without having the changes affect the first chart? ...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

adding a leading 0 to only the numbers with 4 digits
I'm converting a bunch of data and during the import Excel dropped the leading zero from all the lip codes. Since this is about 15,000 records, I'd like a way to add a leading zero to only the zip codes with 4 characters. Any ideas? thanks Swamp, Format/Cells/Special/Zip Code Beege "SwampYankee" <johndillworth@gmail.com> wrote in message news:1147451549.294898.147230@v46g2000cwv.googlegroups.com... > I'm converting a bunch of data and during the import Excel dropped the > leading zero from all the lip codes. Since this is about 15,000 > records, I&...

Wrapping text in a cell
In a single cell, suppose I want text to appear on two lines. Viz: Case One Case Two How do I do that so that I specify the wrap point? Thanks! If you are typing the data into the cell use Alt-Enter between each string to indicate where you want a line break to occur. Case One<Alt-Enter>Case Two Alt + Enter -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27741 View this thread: http://www.excelforum.com/showthread.php?threadid=476428 If you...

Is it possible to highlight text?
I'd like to highlight just some of the text in a cell - not the entire thing. Thanks Select the cell and in the edit bar you can then select (highlight) some of the text with the mouse and format how you wish, eg bold, different colour, different background colour, different font/font size etc. Hope this helps. Pete This will NOT work in a formula. Works only if all text. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Pete_UK" <pashurst@auditel.net> wrote in message news:1139215455.134469.285660@z14g2000cwz.googlegroups.com... > Select the cell ...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

Auto expanding X Axis for Chart
I have a chart setup, currently with reference to 31 cells width. The chart looks great if the user places a value in each of the 31 cells, however if there are only say 20 values placed across the cell row, the chart is displayed over to one side, with nil values displayed where no dat values were entered. Is there a way to have the chart automatically vary the width depending on the amount of values entered across the refernced row of cells? If there was only 20 values entered, then the chart would show 1-20 charted and leave out the 21-31. Corey.... EXCEL 2007...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Page Number in Repeat Rows area
Is there a way that I can put a Page Number in the Repeat Rows area of a sheet - and get it to update when I print? I have found one piece of VBA that puts a page number within a cell but it is only updated if the cell is outside the Repeat Row area. When it is within the Repeat Rows area I just get a page number of 1 corresponding to the original row location.. I do some VBA programming but am not an expert. TIA cheers Chris Nothing comes to mind that doesn't require VBA code. You can paste the following in the sheet header (File | Page Setup) and page x of y will print on ea...

Publishing Microsoft Excel Charts to the Web
I routinely publish Excel charts to a web site for viewing in a web browser. When I do so, the charts are too large to fit on one screen without scrolling. Is there a way to make the charts fit the size of the user's browser window when you publish them? Jim - Embed the charts in a worksheet (right click, Location, As Object In: Sheet Name), and size them to fit before exporting. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Jim wrote: > I routinely publish Excel charts to a web site f...

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

Modifing data to show up better in chart.
My Y1 axis is from 0-250 my Y2 axis is from 0-9000 When graphing percentages the line shows up at the very bottom since 99% < 1 is there a way I can multiply these percentages by 100 to show them as 9900% = 99 Normally you would just make then a 2nd axis but as you can see I already have a second axis of 0-9000 In article <A9E6F75B-7307-4291-BC7D-729F9DF88D50@microsoft.com>, MikePunko@discussions.microsoft.com says... > My Y1 axis is from 0-250 my Y2 axis is from 0-9000 > When graphing percentages the line shows up at the very bottom since 99% < 1 > is there a way I...

Movie of Charts desired
I'm having alot of trouble trying to show charts repeatedly. I change the data that is the 'source' of the chart, but even with a 'refresh' it doesn't want to show anything, till the last one. What gives? Any general hints or clues appreciated. Thanks. Ken Where is the chart(s)? How are you changing the source? What do you refresh? How do you refresh? What doesn't show until what last one? -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <...

Creating Charts for Use in Powerpoint
I have a series of charts that I need to create for a Powerpoin presentation. I thought it would be easy to just create the charts/graphs in Exce and then just cut and paste to the PP-Slides. I'm finding that it isn' quite that easy. Here are some of my problems that I hope someone might be able to she some light on and give me some direction. 1. In Excel how do you control the size of the chart? - When I create a chart 1 and define the data ranges I get a goo looking chart then I thouht, why reinvent the wheel so I just copy th chart to create chart 2 and change the data range, but...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

vba to prevent display of default series in doughnut chart
I am generating a doughnut chart using VBA and am having two immediat issues: 1) I am using the following line to ensure that the default chart tha is generated when I invoke the Charts.Add method is a doughnut chart: Application.SetDefaultChart FormatName:=xlDoughnut Is there a more elegant approach to ensuring that any chart create ends up being a doughnut chart, maybe when invoking the Charts.Ad method? I have tried to specify ChartType = xlDoughnut after th Charts.Add method but this does not do the trick every time (see cod snippet below). 2) When the doughnut chart is added (immediat...

Putting Excel Charts into a Word document that's getting too big
I am creating a Word document (our Annual Report) in which I will have many charts and graphs that were created in Excel. Currently, there will be at least 26 graphs/charts in the document. In the past, there have apparently been problems with not having enough space to save the Word document because it gets too big. The document itself is only about 36 pages, but, as I mentioned, within the 36 pages are at least 26 graphs and charts. I'm looking for a way to copy the graphs/charts into Excel without it taking so much memory. One solution we thought of was to only paste the gra...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

SOP Original Number on RM Detail Hist Aged Trial Balance
I'm trying to add the SOP Original Number (SOP Order Number) to the RM Detail Historical Aged Trial Balance. I've linked the Sales Transaction History Table to the RM_HATB_Document_TEMP table. I then added a dummy Restriction where Original Number = Origianl Number. However, every time I try to run this new report, I still get the following error: Inconsistent restriction/sort expression. Please add the following restriction 'D03304Original Num = D03304Original Num'. Has anyone successfully added this field to this report? What am I doing wrong? I was able to link...

How do I create a chart in Excel using data from two worksheets
I am trying to create a chart using data from two separate worksheets but it will only allow me to use the information from one. Here is one way: I made a chart from data in A10:B20 on sheet1 (A was the x-category data) I copied some cells from Sheet2, right clicked in chart area of chart and used Paste After the copy, I could have left clicked the chart and used Edit | Paste Special to specify more about the data I want to paste. Try it and come back with more questions. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "cdptuesdays" <...

html or plain text in email
Using MS Outlook 2000, when I want to reply to or forward an email, how do I get that reply or forward to be in html rather than plain text. When I create a new email it defaults to html, but when I reply or forward, it defaults to plain text. Thank you. using the forward or reply/all from menu the message will take on the config of the original sender. So the sender of the this message has txt as default editor. If you want to use your default (html) you will have to create new message and copy all text out of original past in your new message. Hope this helps. >-----Original ...

X Axis needed for Line Chart #2
I have created two line images on a Line Chart using several data points. Now I would like to have an x axis that shows a marker for every 1/2 hour. Example: .5 1.0 1.5 2.0 2.5 3.0 and so on for up to 5.0 to represent hours. I created the data for these time points that coorespond to my data for line images but do not know how to show them in my x axis as markers. Any idea? My x axis shows nothing? Y axis is fine. I have data scale markers there. -- FL Does the chart know you have X data? Go to Chart menu > Source Data > Series tab. Is there anything in the ...