Chart ignoring blank cells with function

[patphilly]

I need to make a simple XY chart out of 2 columns. This is of course obvious but in my case, the data at taken from another sheet.

The values in the X series are simply copied from another sheet "Sheet2". I could possibly simply recall those values by using the obvious formula
= Sheet2!A1

but since these values are in the Date format, there are some issues. In cases where the cell A1 in Sheet2 is indeed blank - and there are such cases - the above formula would by default yield a zero value, which for the date format provides January 1st, 1904. In order to ensure that blank cells in Sheet2 are left blank in my current page, I have thus used

= IF(NB(Sheet2!A1)=1;Sheet2!A1;"")

(that is, if there is a value in that cell, it provides that value, but leaves the cell blank if there is no value).

Fine, this is my column A, corresponding to the X values in my desired XY chart.

Then, the B column simply numbers the dates in column A from the oldest to the most recent:
the corresponding B1 cell would thus have the formula

= IF(NB(A1)=1; RANK(A1;A$1:A$100;1); "")

(Again, if there is no value in the nearby cell A1, it gives a blank result, and otherwise provides the rank of that date with respect to the whole column (here assumed to go from A1 to A100).

To summarize, by columns A and B look like this:

A B
1 = IF(NB(Sheet2!A1)=1;Sheet2!A1;"") = IF(NB(A1)=1; RANK(A1;A$1:A$100;1); "")
2 = IF(NB(Sheet2!A2)=1;Sheet2!A2;"") = IF(NB(A2)=1; RANK(A2;A$1:A$100;1); "")
3 = IF(NB(Sheet2!A3)=1;Sheet2!A3;"") = IF(NB(A3)=1; RANK(A3;A$1:A$100;1); "")
4 = IF(NB(Sheet2!A4)=1;Sheet2!A4;"") = IF(NB(A4)=1; RANK(A4;A$1:A$100;1); "")
5 = IF(NB(Sheet2!A5)=1;Sheet2!A5;"") = IF(NB(A5)=1; RANK(A5;A$1:A$100;1); "")
6 = IF(NB(Sheet2!A6)=1;Sheet2!A6;"") = IF(NB(A6)=1; RANK(A6;A$1:A$100;1); "")

Now if we assume that Sheet2 contains the following data:

A
1 12.10.2008
2 01.01.2006
3
4 07.06.2004
5
6 12.03.2007

Then the current sheet yields the following columns A and B:

A B
1 12.10.2008 4
2 01.01.2006 2
3
4 07.06.2004 1
5
6 12.03.2007 3

All I want next is to make an XY plot of that column B as a function of the column A. But it doesn't work! It provides the right chart only if I select a range of data with no blank line (e.g. if I select only the first 2 lines in the above example). Otherwise it doesn't understand that it needs to list the B values as a function of the A values, and I get an ugly scattered plot.

I realize this may seem long and hard to understand, but maybe somebody can help?
Or if you have another idea on how to avoid the "zero" date value "01.01.1904" when Sheet2 contains blank cells, that could be useful...
THanks!

[teylyn]

Can you possibly post a sample file? That will be so much easier to analyse.

What is the NB function you are using? Is that a UDF?

[patphilly]

Here is a sample file.
You will see that in Sheet2 which lists the original data, the chart is correct and works fine. However in the Main sheet, the same procedure doesn't work.

PS: Regarding the "NB" function that I mention: I have been using the French version of Excel and I'm not sure how it's called in the English version. Hopefully it will be translated automatically in the file I have now provided...

[teylyn]

In the main sheet, the blank cells are not truly empty, but contain an empty character. This renders the cell as text, and as such, it can not be slotted into the X axis. So the whole X axis is treated as a category axis, with the values being plotted in the order they appear in the data table.

Change the formula in column B to
=IF(COUNT(Sheet2!B1)=1,Sheet2!B1,0)

and in column C to
=IF(COUNT(B1)=1,RANK(B1,$B$1:$B$100,1)-1,"")

Format column B with custom format
d.mm.yyyy;;;

and column C with custom format
0;;;

This will make the zero values disappear.
Now format your X and Y axes to the same scale as the other chart and the two charts will look the same.

[patphilly]

Thanks, that's a good point.

However, it doesn't quite do the trick. Indeed, while they are now not visible, several dates in column B are now zero. And this affects the rank in column C. Indeed, in the sheet, there are 49 blank dates (which are now "Zero" dates) and 51 real dates. I would like logically the 51 dates to be ranked from 1 to 51, but now it takes the zero dates into account: Therefore the real dates are now numbered from 50 to 100!

[patphilly]

In other words, a nice workaround would be if we can calculate the ranks of "all dates in column B which are >0"

But otherwise, is there no other way to specify we want a "blank" date cell without assigning a text format "" or assigning a zero value?

[teylyn]

patphilly,

I see what you mean. All the empty dates are now zero and will receive a rank of 1

Therefore, I had amended the ranking formula. Where before it was

=IF(COUNT(B1)=1,RANK(B1,$B$1:$B$100,1),"")

I first suggested

=IF(COUNT(B1)=1,RANK(B1,$B$1:$B$100,1)-1,"")

and you're right, the Ranking values are now much higer. But if you change it to

=IF(COUNT(B1)=1,RANK(B1,$B$1:$B$100,1)-49,"")

then it is right again.

In the chart you initially attached, the maximum ranking (=max(C1:C100) was 51. With the formula above, the max is still 51 and the minimum value is -48. You can easily biff these small values by setting your chart axes scales and you'll be good.

Will that work for you? See attached

[patphilly]

Thanks for the new suggestion.
However this is still not good... Indeed, dates in Sheet2 will be periodically filled in, slowly filling the blanks as each event occurs. Therefore there will not always be 49 blanks.

I guess that instead of subtracting the fixed number 49, we could subtract the number of "zero" dates in the series, so something like COUNTIF(B1:B100;0). I guess that could work?

However this all seems quite complicated. This whole process of assigning a zero value to blank dates, then hiding these zero values, and then resubtracting the number of such occurrences to adjust the ranks is not too elegant...
Is there really no way to produce a graph that ignores the "" values (or doesnt necessarily take them as text)? Or to force the program to understand "" as a blank item in Date format?

[teylyn]

Is there really no way to produce a graph that ignores the "" values (or doesnt necessarily take them as text)? Or to force the program to understand "" as a blank item in Date format?

Not that I'm aware of.

But you seem to have a good workaround with the Countif().

It only seems clumsy when you set it up for the first time. Once it's in place, it's transparent and will work.

I'm sure that someone could write several screens full of VBA to achieve the same thing, but, personally, I don't think these formulas are too much to conquer.

The thing is that a formula that delivers a "" is not considered as a truly blank cell when it comes to charting. Also, any function that evaluates that cell and finds a "" will treat this cell differently to a truly blank cell. That's a fact of life ... ehrrmmm, Excel, and you have to live with it or try Open Office instead.

cheers

[patphilly]

OK, thanks a lot!