0

In my Excel worksheet, I have a cell containing the following formula:

=IF(COUNT(Y11:AA11)>0,SUM(Y11:AA11),#N/A)

with the goal of a chart later using the display of the cell in a graph (excluding the display of any 0 values), which it does.

The issue I have is that there are a lot of #N/A display values that look messy, so I want to use conditional formatting to make the cases when #N/A is displayed have white text. So far every attempt makes every value have white text regardless of if the display value is #N/A or a number. Does anyone know a way to do this?

Things I have tried:

  • Using conditional formatting with formula condition of =ISERROR($X$4:$X$37), applied to the range $X$4:$X$37.  (All cells in the range are formatted to use white text; valid numbers and error values.)

  • Using conditional formatting with formula condition of =ISNA($X$4:$X$37) (same result).

  • Using conditional formatting with affectation only to duplicates (Displaying number still affected)

  • Changing formula in the cells (X4:X37) to =IF(COUNT(Y11:AA11)>0,SUM(Y11:AA11),"#N/A"), then conditional formatting that text. (Zero value now displays on graph since "#N/A" is text and #N/A was not.)

Here’s an image of what I’ve tried:
Conditional Formatting Rules Manager

  • Try putting #N/A in quotes in your formula: =IF(COUNT(Y11:AA11)>0,SUM(Y11:AA11),"#N/A") – cybernetic.nomad Apr 9 '18 at 20:12
  • When I use "#N/A" Excel interprets this as text and displays a 0 value in my chart instead of no value. – Concerned Data Miner Apr 9 '18 at 20:16
  • @Scott updated entry to reflect your suggection – Concerned Data Miner Apr 10 '18 at 13:47
  • Thanks for updating your question.   I have embedded the image for you.   (As you have discovered, you must have 10 reputation points to show an image; that’s to deter spam.   It’s routine for us users who have ≥ 10 rep to review images and embed them if they aren’t spam.)   But (1) Máté Juhász said that the answer is to use a formula like =ISERROR(A2) to determine which cells to format.   In your example, you should use =ISERROR(X4).   The point is that the argument to any of the ISxxx() functions should be a single value, not a range.   (I can’t even figure out   … (Cont’d) – Scott Apr 10 '18 at 17:45
  • (Cont’d) …  what it’s doing when you call them with a range, but that’s a mystery puzzle I’ll leave for another day.)  (In your defense, I’ll point out that, while the ISERROR documentation repeatedly says that you pass it A value, I don’t see where it ever says that the parameter must not be a range or an array.)  So try that; the Conditional Formatting mechanism takes care of handling each cell from X4 to X37.  And don’t use $ signs.  If you say =ISERROR($X$4), then the entire range will turn white if X4 is an error (and vice versa).   … (Cont’d) – Scott Apr 10 '18 at 17:46
1
  • Select your data you want to format
  • go to home - conditional formatting - new rule
  • select "use a formula to determine which cells to format"
  • enter formula:
    =ISERROR(A2)
    (replace A2 to address of active cell)
  • select the format you need (in the example below I've used yellow highlight to make my answer clear)

enter image description here

  • When doing this my numbers displaying are still affected as well. – Concerned Data Miner Apr 10 '18 at 13:36
  • do you have other conditional formatting rules too? Can you post a screenshot of your data? – Máté Juhász Apr 10 '18 at 13:38
  • I'm not sure how to post a screenshot, but the only other conditional formatting rule is to highlight a day that occurs on a weekend in yellow, and the formatting I am struggling with makes font white. – Concerned Data Miner Apr 10 '18 at 13:50
  • Isn't "stop if true" checked? (support.office.com/en-us/article/…) – Máté Juhász Apr 10 '18 at 13:55
  • For the formatting intending to whitefont the #N/A? No, when this is checked the entire cell goes white with or without the display of the cell being a #N/A or number. – Concerned Data Miner Apr 10 '18 at 13:59
1

The problem is the absolute references in your ISNA() function.

ISNA() is ONLY returning the result for the first cell in the range used. So if it activates the CF-ing, it will do so for all the cells the rule is applied to because no matter the cell applied to, it will never look to anything other than the first cell in that absolute range.

ISNA() will never look at the other cells in the range even if you let them be relative references.

BUT it works if you at least let the row reference be relative. That is because the range examined by ISNA() keeps changing so the first cell is the cell you actually want looked at. That happens on each new row so while it looks the same, pretty much, you are actually changing the first cell in the range, without seeing it happen visibly, and so ISNA() is looking to a new first cell each time.

If you make the change and look at the CF rule with the cell highlight in different rows each time, you will see the shown rule changing that range.

(You are using the function's failure to work as one might think it would to actually be the accomplishing operator achieving your goal. It's sort of like wanting a pile of trash removed, convincing people that the stuff in the pile is valuable, looking away while they steal some, and eventually having no trash left in that. You are using failure to achieve a worthwhile goal.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.