If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel chart - result of formula = " " don't want to see in chart
I have built a simple line graph, showing last year sale figures, this
years budget and this years actuals. I have 12 months worth of data for the first two categories, but only two months for actuals. The problem I have is that the data for the graph is being taken from a table which gets its information from a larger data block using a lookup function. The problem I have is that the data for actuals includes a number for January and February but nothing for March onwards. To insure that there is nothing there I have used an if funtion to return a " " if there is no number or the number itself if there is. The chart shows the " " as a zero, but all I want to see is a line representing January and February. I know this is probably a really basic thing but I just can't find the answer. HELP!!! Thanks Simon --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Excel chart - result of formula = " " don't want to see in chart
Hi
try changing your IF function to something liek =IF(a1"",A1,NA()) so instead of returning " " return NA() you may use conditional formating to hide these output (use a white font color for this) -- Regards Frank Kabel Frankfurt, Germany I have built a simple line graph, showing last year sale figures, this years budget and this years actuals. I have 12 months worth of data for the first two categories, but only two months for actuals. The problem I have is that the data for the graph is being taken from a table which gets its information from a larger data block using a lookup function. The problem I have is that the data for actuals includes a number for January and February but nothing for March onwards. To insure that there is nothing there I have used an if funtion to return a " " if there is no number or the number itself if there is. The chart shows the " " as a zero, but all I want to see is a line representing January and February. I know this is probably a really basic thing but I just can't find the answer. HELP!!! Thanks Simon --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Excel chart - result of formula = " " don't want to see in chart
Hi
for the conditional formating issue. - select the cells for which an NA error can occur - goto the conditional format dialo - enter the following formula =ISNA(cell_reference) where cell_reference ist the first cell of your selection. e.g. =ISNA(A1) - now choose a white font for these condition -- Regards Frank Kabel Frankfurt, Germany Ant_Emberson wrote: I read this topic with glea! It was just what I was looking for... So many thanks for that This is my formula =IF(SUM('Enter Data'!O5/1.175)*('Enter Data'!P5/100)=0,NA(),SUM('Enter Data'!O5/1.175)*('Enter Data'!P5/100)) forcing the formula to give #N/A where there is no value in the original data, you mentioned using the conditional formatting to "Hide" the #n/a, no matter what I try the 'N/A will not go white. Can you help? Thanks |
Thread Tools | |
Display Modes | |
|
|