Make an if formula result empty, not blank

Question

I have a problem. I did create a formula that fills a cell only if a date criterion is met. Otherwise it leaves it blank. Then I plot the resulting values. Now, the problem is that the cell is not empty but blank, therefore when I plot it, its value is set to zero and the line continues. I'd like the plot to look like the case in which the cells are empty In other words, I'd like the formula to give me a value that when plotted will be treated as empty. Apparently "" does not make the trick I do not know how to upload the file, but I made a screenshot that should be self explaining: what the red arrow indicates is the problem.

I want to use the formula of example 1 and have the chart of example 2

Thank you very much in advance for your help! enter image description here

Answer 1

Based on your image and your follow-up comment, it appears that you want both to exclude the blank cells from being plotted as 0 on the graph and to have those cells display as blank rather than #N/A. Unfortunately, it looks like Excel 365 still does not support that combination directly.

But to achieve the visual result that you seem to be after, you could use this workaround (for this example, I have the "current" date in A1 - the cell with the value to compare the list against - and the list of "dates" data in cells A4-A15):

Cell A1: (current date)

Column A: (sequence of dates, starting in A4)

Column B (formula column starting in B4) - If the date in Column A is less than the current date (cell A1), then return a random number; otherwise return an empty string:

=IF(A4<$A$1,RAND(),"")

Column C (formula column starting in C4) - if the date in Column A is less than the current date in cell A1, copy the value in Column B; otherwise return result NA():

=IF(A4<$A$1,B4,NA())

Build the chart with the data in A4-A15 and C4-C15, and enable the "Show data in hidden rows and columns" option (right-click the chart > Select Data > Hidden and Empty Cells). Then hide column C.

This is a bit of a kludge, but it achieves the combination of visual results that you seem to be after.

I used integers rather than dates in these examples, but they illustrate the results:

Column C hidden

All columns visible

Also note this related question on the inability of an Excel formula to return an empty cell.

Answer 2

The answer by Memetis is super helpful! Just adding a bit more info (Unable to comment because I am new user)-- you can hide the #N/A's by using conditional formatting: Select the cell you wish to hide the #N/A; Pick 'format cell using a formula'; In the formula bar use =isna(cell); And set the font color to be white; Use format painter to apply this format to wherever you wish.

GL