Tell me more ×
Cross Validated is a question and answer site for statisticians, data analysts, data miners and data visualization experts. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm currently building a chart with Excel out of a fair number of lines of statistics.

Since there are too many lines, i cannot plot them all, otherwise the chart would become unreadable.

I'm currently selecting the lines and values by hand, which is a tedious process. I'm trying to automate some of this work.

The question : I want to only plot the 5 highest lines of statistics. At any point in time (the "columns"), I can easily extract the 5th highest value. I then know if a value is among the 5 best ones ("IF" function).

For a cell value to not be plotted, it's enough to keep the cell empty. So, if a value is not among the 5 best ones, i want the cell to be considered empty, so that it is not plotted.

The problem is, i don't know how to "not output anything" or "output something which will not be plotted" from an "IF" function. For example, if output value is "", nothing is displayed in the cell, but it is still considered like a zero by the chart.

share
An illustration would help, because the answer depends on what you mean by "line," by "line of statistics," and what kind of plot you would like to make. That would also help us determine whether this question belongs here or would be better on SO. – whuber♦ Nov 25 '11 at 20:53
Well, that's stupid of you, since the question is not "vague" at all, and i guess other people will have a similar question in the future. It took me quite some time to find an answer to this problem, it is provided here free of charge, for anyone who meet the same issue. But all this will disappear. I don't need to be bashed by overly confident level 60 WoW players. Great intervention whuber. You are making this site more useless every day. – tryingtoremovetheuserprofile Nov 25 '11 at 22:34

locked by whuber♦ Nov 26 '11 at 1:11

This post has been locked due to the high amount of off-topic comments generated. For extended discussions, please use chat.

closed as not a real question by whuber♦ Nov 25 '11 at 21:59

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

1 Answer

up vote 1 down vote

OK, found.

A cell with a formula is not considered empty, so i cannot make it behave like an empty one with an "IF" formula.

However, it is possible to generate a special value with the function "NA()". This value will not be plotted. The table now looks ugly, with a lot of #N/A everywhere, but at least the chart plots correctly.

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.