Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Empty strings appear as zeros
I have some formulas that return empty strings if certain conditions
exist. The intention is that charts NOT show data in these cases. Yet, they show up as zeros. I've read on here about the use of NA(), and that works fine, but then if I have another formula that looks at these values for the maximum value (of the data series, to automatically adjust the max scale), it won't work because of the NA() error. What I would like is to have the empty string values NOT appear in the chart, and I'm wondering if there's another solution other than forcing the use of NA()?? Any suggestions appreciated. Paul Martin Melbourne, Australia |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Empty strings appear as zeros
I should also mention that I've seen the posts about setting the Chart
Options to plot empty cells as 'Not plotted' (which has no effect) and 'Interpolated' (which is not enabled), so this seems to be no help. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Empty strings appear as zeros
Hi,
That option will only apply to cells that are truly empty. You can either use another column with the variation of the formula returning "" so the maximum scale value can be determined. Or perhaps modify your formula to determine the maximum value. The array formula, commit using CTRL+SHIFT+ENTER, will return the maximum value of the cells B2:B6 ignoring any that contain #N/A =MAX(IF(ISNA(B2:B6),"",B2:B6)) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Paul Martin" wrote in message ... I should also mention that I've seen the posts about setting the Chart Options to plot empty cells as 'Not plotted' (which has no effect) and 'Interpolated' (which is not enabled), so this seems to be no help. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Empty strings appear as zeros
You can leave the #N/A errors so they don't appear on your chart, and use
the following formula to calculate the maximum. Assuming your data is in B1:B10, =MAX(IF(ISNA(B1:B10),"",B1:B10)) entered as an array formula with Cntrl-Shift-Enter will give you the max of your data even if it does contain #N'A error. Dave url:http://www.ureader.com/msg/10295975.aspx |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Empty strings appear as zeros
My favorite technique is to have one range for tabular display, one range
for the chart source, and one range for subsequent calculations. They all link to the original data, so they're all "correct", but each is optimized for its own purpose, and therefore each has variations on the formulas used, to help in this modification. Cells are cheap. Worksheets are cheap. Your time and frustration are not cheap at all. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Paul Martin" wrote in message ... I have some formulas that return empty strings if certain conditions exist. The intention is that charts NOT show data in these cases. Yet, they show up as zeros. I've read on here about the use of NA(), and that works fine, but then if I have another formula that looks at these values for the maximum value (of the data series, to automatically adjust the max scale), it won't work because of the NA() error. What I would like is to have the empty string values NOT appear in the chart, and I'm wondering if there's another solution other than forcing the use of NA()?? Any suggestions appreciated. Paul Martin Melbourne, Australia |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Empty strings appear as zeros
While awaiting a reply, I had already employed Jon Peltier's method of
an additional set of calculations, which is working fine, though I was hoping there was a solution with less redundancy. As much as I don't want to leave array formulas in the hands of users, Andy Pope's and Dave Curtis' suggestions look quite appealling as it will remove the need for a separate sheet and second data set. Both solutions are equally valid, depending on preference. Thanks guys for each of your replies. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CSV and Numeric Strings with Leading Zeros | Excel Worksheet Functions | |||
pivot tables, calculated fields, empty cells and zeros | Excel Discussion (Misc queries) | |||
Charting with missing data or empty strings from vlookup() | Charts and Charting in Excel | |||
remove leading zeros from text strings | Excel Worksheet Functions | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) |