Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formating to keep the #N/A error from being visible
I am putting together a line graph that will be updated hourly. I do not want
it to show the next data point until the update is entered on the graph. When the related cells on the graph are blank though the line graph dips to zero for that data point. My formula is basically: =IF(A10,A1,"") The advice I recieved: Excel treats "" as text, with a numerical value of zero. And even if A1 is blank, its value is brought over as zero in the first formula. Use this formula instead: =IF(A10,A1,NA()) This isn't plotted in a line or XY chart. It leaves an ugly #N/A error in the cell, which you can hide with conditional formatting. So... I either need a new solution to the original question or help with conditional formatting so I don't have to see the ugly #N/A error. Thanks alot! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formating to keep the #N/A error from being visible
Hi,
In conditional formatting use: =ISERROR(A1) Cheers! Jean-Guy "irvine79" wrote: I am putting together a line graph that will be updated hourly. I do not want it to show the next data point until the update is entered on the graph. When the related cells on the graph are blank though the line graph dips to zero for that data point. My formula is basically: =IF(A10,A1,"") The advice I recieved: Excel treats "" as text, with a numerical value of zero. And even if A1 is blank, its value is brought over as zero in the first formula. Use this formula instead: =IF(A10,A1,NA()) This isn't plotted in a line or XY chart. It leaves an ugly #N/A error in the cell, which you can hide with conditional formatting. So... I either need a new solution to the original question or help with conditional formatting so I don't have to see the ugly #N/A error. Thanks alot! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formating to keep the #N/A error from being visible
I'd suggest creating a dynamic range in the worksheet that includes only
non-blank data points, then using that range as your source. See http://peltiertech.com/Excel/Charts/...umnChart1.html for a good description. In article , irvine79 wrote: I am putting together a line graph that will be updated hourly. I do not want it to show the next data point until the update is entered on the graph. When the related cells on the graph are blank though the line graph dips to zero for that data point. My formula is basically: =IF(A10,A1,"") The advice I recieved: Excel treats "" as text, with a numerical value of zero. And even if A1 is blank, its value is brought over as zero in the first formula. Use this formula instead: =IF(A10,A1,NA()) This isn't plotted in a line or XY chart. It leaves an ugly #N/A error in the cell, which you can hide with conditional formatting. So... I either need a new solution to the original question or help with conditional formatting so I don't have to see the ugly #N/A error. Thanks alot! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formating to keep the #N/A error from being visibl
So if I am understanding you... in the conditional formatting i would select
"equal to" and then type "=ISERROR(A1)"? If so it is not working. Please clarify if I misunderstood. thanks "pinmaster" wrote: Hi, In conditional formatting use: =ISERROR(A1) Cheers! Jean-Guy "irvine79" wrote: I am putting together a line graph that will be updated hourly. I do not want it to show the next data point until the update is entered on the graph. When the related cells on the graph are blank though the line graph dips to zero for that data point. My formula is basically: =IF(A10,A1,"") The advice I recieved: Excel treats "" as text, with a numerical value of zero. And even if A1 is blank, its value is brought over as zero in the first formula. Use this formula instead: =IF(A10,A1,NA()) This isn't plotted in a line or XY chart. It leaves an ugly #N/A error in the cell, which you can hide with conditional formatting. So... I either need a new solution to the original question or help with conditional formatting so I don't have to see the ugly #N/A error. Thanks alot! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formating to keep the #N/A error from being visibl
Hi,
No, use "Formula is:" instead of "Cell value is:" Cheers! Jean-Guy "irvine79" wrote: So if I am understanding you... in the conditional formatting i would select "equal to" and then type "=ISERROR(A1)"? If so it is not working. Please clarify if I misunderstood. thanks "pinmaster" wrote: Hi, In conditional formatting use: =ISERROR(A1) Cheers! Jean-Guy "irvine79" wrote: I am putting together a line graph that will be updated hourly. I do not want it to show the next data point until the update is entered on the graph. When the related cells on the graph are blank though the line graph dips to zero for that data point. My formula is basically: =IF(A10,A1,"") The advice I recieved: Excel treats "" as text, with a numerical value of zero. And even if A1 is blank, its value is brought over as zero in the first formula. Use this formula instead: =IF(A10,A1,NA()) This isn't plotted in a line or XY chart. It leaves an ugly #N/A error in the cell, which you can hide with conditional formatting. So... I either need a new solution to the original question or help with conditional formatting so I don't have to see the ugly #N/A error. Thanks alot! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formating to keep the #N/A error from being visibl
Thanks JE, dynamic range is just what I needed. And the tutorial was a big
help. "JE McGimpsey" wrote: I'd suggest creating a dynamic range in the worksheet that includes only non-blank data points, then using that range as your source. See http://peltiertech.com/Excel/Charts/...umnChart1.html for a good description. In article , irvine79 wrote: I am putting together a line graph that will be updated hourly. I do not want it to show the next data point until the update is entered on the graph. When the related cells on the graph are blank though the line graph dips to zero for that data point. My formula is basically: =IF(A10,A1,"") The advice I recieved: Excel treats "" as text, with a numerical value of zero. And even if A1 is blank, its value is brought over as zero in the first formula. Use this formula instead: =IF(A10,A1,NA()) This isn't plotted in a line or XY chart. It leaves an ugly #N/A error in the cell, which you can hide with conditional formatting. So... I either need a new solution to the original question or help with conditional formatting so I don't have to see the ugly #N/A error. Thanks alot! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating | Excel Worksheet Functions | |||
Conditional formating -identify blank cell | Excel Worksheet Functions | |||
How do i copy conditional formating formulas from 1 row to rest | Excel Discussion (Misc queries) | |||
conditional formating - wildcards | Excel Worksheet Functions | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) |