=if(a2=a3,a3,<how do I make this a NULL value?>) 4

[danhauer]

Hi!


I am making a graph based on the formula =if(a2=a3,a3,null) only the null portion doesn't work, of course. I can't use "" or a space (I even tried just a comma with nothing after it), because the graph interperets anything other than nothing (null) as a zero, when actually on the graph it should just not show up as a point in that series.

So, does anyone know how to do an "IF, then, NULL"-type of formula?

I would truly appreciate anyone's ideas. =)

Thanks,

danhauer

 

[JVFriederick]

Have you played around with Tools Options Chart settings?
I don't know offhand the impact, but it might help.

 

[danhauer]

Thanks for the response. I checked it out, and it will treat "empty cells" as... Trouble is, I can't get the cell to stay empty using my if() function. It was definitely worth a shot, though! Thanks!

 

[Dreamboat]

Can you create a quick macro that will copy your cells with the formulas to another sheet, then (while range still selected) find zeroes and replace with >nothing>?

Then use that range to create your chart.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Dreamboat]

Oops, when you paste, you'll have to Edit-Paste-special, Values.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[JVFriederick]

I think you might be better off sorting your data and adjusting the chart extents to exclude the null rows.

 

[ilses]

=IF(A2=A3,A3,NA())

It does present you with the #NA in the cell on your data sheet, but if you like you can get rid of that by conditional formatiing.

IS

 

[dawnd3]

I believe if you go to Tools, Options and to the view tab, turn off zero values, that should work. I just tried it out and it worked for me. So, in your if statement you can have it put a 0. Dawn

 

[danhauer]

Wow...thanks for all of your responses! After examining and trying everyone's suggestions, so far the only way I have been able to avoid charting empty-looking cells as a zero is to use the NA() function that ilses suggested. That keeps the point off of the chart as a zero, but keeps the points before and after "connected" whereas a null value will show a break between the points. Very interesting... I think I can go with this. Thanks again everyone!

It sure would be nice to just say =if(a2=a3,a3,null)

danhauer