Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Empty Cell calculation
I have a column with formulas.
When formulas return empty cell (""), the chart seems to handle it as a 0. I do not want to display those data points at all. I tried to use NA() instead of . The chart seemed to be o.k. but now I have a problem finding MAX/MIN Values of this column because I have there now both numbers and #N/A. Thanks in advance, Elan |
#2
|
|||
|
|||
Elan,
1) Use a second column of formulas to find your MAX and MIN values 2) Use an array formula (entered with Ctrl-Shift-Enter) like =MAX(IF(ISERROR(A1:A5),0,A1:A5)) which will ignore the #N/A values. HTH, Bernie MS Excel MVP "Elan" wrote in message ... I have a column with formulas. When formulas return empty cell (""), the chart seems to handle it as a 0. I do not want to display those data points at all. I tried to use NA() instead of "" . The chart seemed to be o.k. but now I have a problem finding MAX/MIN Values of this column - because I have there now both numbers and #N/A. Thanks in advance, Elan |
#3
|
|||
|
|||
"" is not an empty cell, it is a zero length string. Worksheet formulas
cannot return empty cells. Text (including zero length strings) plot as zero. #N/A doesn't plot, but also doesn't break connecting lines. If you need the cell to behave EXACTLY like an empty cell, you will have to delete the formula. For a dynamic worksheet the process of deleting or restoring formulas as needed could be automated via a macro tied to a change event. Jerry Elan wrote: I have a column with formulas. When formulas return empty cell (""), the chart seems to handle it as a 0. I do not want to display those data points at all. I tried to use NA() instead of . The chart seemed to be o.k. but now I have a problem finding MAX/MIN Values of this column because I have there now both numbers and #N/A. Thanks in advance, Elan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Bar Chart, can we display both figures and their respective %a. | Excel Discussion (Misc queries) | |||
AutoFIll -by dragging the bottom-right corner of the cell | Excel Discussion (Misc queries) | |||
how to create a variable column in cell reference | Excel Worksheet Functions | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
Display a dialog box if cell is empty | Excel Discussion (Misc queries) |