Microsoft Excel 2003 (full product)

I need to make a chart but I only want to chart the non-empty cells. But for some of the cells I use an IF to automatically fill them with a value or leave the cell empty. I used "" for the empty case but the chart does not see that as empty.

e.g., =IF(LEN(B121)=0,"",B121-6000)

Can anyone suggest the proper entry for the TRUE case in the example to create an empty cell?

There really is no way to use a formula to create an "empty" cell since the cell will contain the formula. Here are 2 methods to create a graph that will ignore the blank cells:

1 - This one I've used in the past:

Create an AutoFilter for your data and select "NonBlanks". Only the visible cells will be graphed.

2 - This one I stole without permission from here and then modified:

Let's say your data resides in A1:A10 and a few of those cells are blank due to your formula.

Enter this in B1 and drag it down to B10:

=IF(A1<>"",ROW(),"")

Enter this in C1 and drag it down to C10: (formula split to fit in this post)

=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$10),"",

INDEX(A:A,SMALL(B$1:B$10,1+ROW(A1)-ROW(A$1))))Column C should now contain a contiguous list of the non-blank values from A1:A10.

Graph the values in Column C.

BTW, instead of this...

=IF(LEN(B121)=0,"",B121-6000)

why not use this?

=IF(B121="", "", B121-6000)

Thanks for the input. Here is another idea. In this example column J has the graph Y values (=IF(LEN(B121)=0,"",B121-6000)) and that is where I need an empty cell if B121 is blank. If I can put my formula for column J (the column with the Y values for the chart) into column O and if there is a way to copy the result of the formula to column J then I can create an IF that will do nothing or copy the result of B121-6000 to J. That way J will have empty cells if B121 is blank.

e.g., Column O would have something like =IF(LEN(B121)="","",COPY(J121,B121-6000))

So is there a "copy" function?

Formulas cannot perform actions (e.g. Copy) they can only produce a result within the same cell, based on the inputs to the formula. The only way to get a completely empty cell based on the result of a formula is to use VBA to set the contents of the cell. VBA can either replace (permanently) a formula that created the blank cell or it can look at the contents of 1 cell and set another one to empty.

This could be done automatically with a worksheet change macro that could monitor (for example) a given column and run every time that column is changed.

However, I still don't see how that will solve your problem. If you have blanks cells in the list of data you are trying to graph, won't they still get graphed?

Since you seem to be willing to try something that involves multiple columns, what is wrong with suggestion # 2 from my previous post?

I never suggested there was something wrong with suggestion #2. I have not tried it yet. I just wanted to find out an answer to my new idea first. Regarding your other question "won't they still get graphed", the answer is no. If either the x or y cell is blank then it won't graph that if you set "Plot empty cells as" to "Not plotted" in Options/Chart/Active chart.

I tried the above #2 method. Maybe I was not clear in my original question. Here is my data:

A1-A10: =IF(G1<>"",G1*10,""), =IF(G2<>"",G2*10,""), etc.

F1-F10: date, =F1+1, =F2+1, etc.

G1-G10: 100, 200, 300, 400, 500, blank, blank, blank, blank, blankI want F on the X axis and A on the Y axis. But I don't want to graph F or A if A appears blank (or G is blank).

I tried Option # 1 and it seemed to work for the example you gave, but not in some other cases. I used the information in your latest post and got this:

A B C D E F G 1 1000 01/01/10 100 2 2000 01/02/10 200 3 3000 01/03/10 300 4 4000 01/04/10 400 5 5000 01/05/10 500 6 01/06/10 7 01/08/10 9 01/09/10 10 01/10/10When I graphed it, using a bar graph, I got 5 bars for the first 5 dates, and then just data labels for the rest.

I then selected A1:A10, and used Data...Autofilter to get a drop down arrow.

I pulled down the drop down arrow and choose Non-Blanks.

The graph changed so that it only showed the first 5 dates and the bars for 1000 - 5000.

However, if I add a value in G7, to get 7000 in A7 and use the same filter, I still get a blank for 01/06/10 but only for 01/06/10, not anything beyond 01/07/10.

I don't know if there is a way to graph your data without getting the X axis labels if the blanks are in the middle of the list. Even if I copy the filtered data to another range, Excel adds X axis labels for the dates that don't have values.

Sorry.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History