Conditional Statement to yield ""(empty cell)

[Red over White]

I have found nearly all of what I am looking for in I want the equation to return "" so that when my graphs point to this cell nothing would read on the charts.

Ok, then try this...

=IF(D2=0,NA(),D2)

Then, if you don't want to see the #N/A in the cells you can use conditional formatting to hide them.

However I am struggling with the conditional formatting element. I use Office Mac:2011. Can anyone off any guidance?

 

[Matty]

Try:

=ISNA(A1)

Change A1 to the Cell of interest.

Matty

 

[p45cal]

Yes, select the range where you want to apply the conditional formatting, and make a mental note of which single cell is the active cell. Once you've done that, go into conditoinal formatting, and opt for the Use a formula to… (or Formula Is: option pre-xl2007) and use Matty's formula, substituting the address of that active cell WITHOUT $ symbols. Choose a font colour to match the background colour of the cell so that you won't be able to see the #N/A

 

[Red over White]

<style> <!-- /* Font Definitions */ @font-face {font-family:"ＭＳ 明朝"; mso-font-charset:78; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-fareast-language:JA;} @page WordSection1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style> Unfortunately this hasn’t had the desired effect, or I am missing something. What I am trying to is with a row of cells which forms the basis of the graph, some of whose results have returned the value of “” or a number. What I am trying to do ensure that if a cell returns “” there won’t be an entry on the graph line as shown on he example below.

<table class="MsoTableGrid" style="border-collapse:collapse;border:none;mso-yfti-tbllook:1184;mso-padding-alt: 0cm 5.4pt 0cm 5.4pt;mso-border-insideh:none;mso-border-insidev:none" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:60.8pt;border:none;border-bottom:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">

5.0​

</td> <td style="width:60.8pt;border:none;border-bottom:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">

5.0​

</td> <td style="width:60.8pt;border:none;border-bottom:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">

5.0​

</td> <td style="width:60.85pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">

“”​

</td> <td style="width:60.85pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">

‘’”​

</td> <td style="width:60.85pt;border:none;border-bottom:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">

5.0​

</td> <td style="width:60.85pt;border:none;border-bottom:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">

5.0​

</td> </tr> <tr style="mso-yfti-irow:1;mso-yfti-lastrow:yes"> <td style="width:60.8pt;border:none;mso-border-top-alt: solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">
</td> <td style="width:60.8pt;border:none;mso-border-top-alt: solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">
</td> <td style="width:60.8pt;border:none;mso-border-top-alt: solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">
</td> <td style="width:60.85pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">
</td> <td style="width:60.85pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">
</td> <td style="width:60.85pt;border:none;mso-border-top-alt: solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">
</td> <td style="width:60.85pt;border:none;mso-border-top-alt: solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="61">
</td> </tr> </tbody></table> <style> <!-- /* Font Definitions */ @font-face {font-family:"ＭＳ 明朝"; mso-font-charset:78; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"ＭＳ 明朝"; mso-font-charset:78; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-fareast-language:JA;} @page WordSection1 {size:595.0pt 842.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --></style>

 

[p45cal]

I'm not sure how Excel graphs handle "", but I do know that it can ignore #N/A (it may need configuring to plot a line joining points either side of #N/A, or plot nothing - but I know nothing of Mac Excel), so my approach would be to leave the #N/A in the cells, but make them invisible to the user with conditional formatting as T.Valko suggested.

 

[Red over White]

p45cal, thanks for your help. I tried the solution but the graph still had the line in the sections where the #N/A was in the cells. To get round that problem I also tried a simple amendment to the Y axis formula within 'Select Data Source', but it wouldn't take that so assume it would take more complicated formulae. Will now look to see if I can resolve the problem through a macro which clears any entry where the value is "".

Anyway thanks.

 

[p45cal]

Which version of Excel is this?

 

[Red over White]

Microsoft Office:mac 2011

 

[p45cal]

Microsoft Office:mac 2011

And of course, I already new that - I should read more carefully what I've written before.
I knew that somewhere I'd seen #N/A was the way to go, John Peltier says so here "or the #N/A error if it doesn't fit. If you use zero or any string (even ""), Excel will plot a zero. Using NA() assures that Excel will skip that point without plotting it." and he usually knows what he's talking about.

Anyway, maybe things have changed since then and "" can also be intentionally not plotted; in Excel 2010 for PC, there's an option:
If you right-click the chart, choose Select Data…, in the bottom left of the dialogue box there's a button Hidden and Empty Cells, have an explore there.

Since posting this I came across John Walkenbach's site where he says (regarding pre-Excel2007 charts): "You can also represent data with the formula =NA() instead of leaving a cell blank. The chart will use interpolation for data cells that contain this formula, regardless of the setting in the Options dialog box."

 

[Red over White]

p45cal

Thanks for your help but it looks as though interpolation will scupper what I was looking to do.