Offset and If Statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All.

I am using an Offset forumula to bring back data in a rank ordered format.

I am using the following formula, which is working fine:
=OFFSET($V$1,MATCH(ROW()-1,LocationRankList,0),-2)

However, the file that this is in is going to be used as a template, for an
automated workbook (to be used with many different sets of data). On other
occasions though the list (locationranklist) will be shorter than on the
original one.

Where data is then not included (because the list is shorter) it brings back
a zero. i do not want to be able to see zero's as charts are being produced
from the data and I do not want these entries to be on them.

I think that I need to use an If statment in my formula to say that if a
zero is present in the original data then not to display anything.

Does anyone know how to do this?
 
How about something like this?

IF(YourOffsetEquation = 0, NA(),YourOffsetEquation)

Values of NA() don't display on graphs.

HTH,
Barb Reinhardt
 
Unfortunately this didnt work, it brought back #N/A which is not ideal as
this shows up in my graphs and also in the tables that i will be exporting to
word.

Any other ideas anyone??

Thanks in advance.
 
Are you usually bringing a number to be used in the chart--or is this a label
for the chart.

If it's usually a number, the the #n/a shouldn't appear in the chart.

If you want to hide the value in the table, you could use conditional formatting
to hide the error (use a white font color on a white fill color).

If that doesn't work, maybe you can use two columns--one to bring back what you
need for the chart and then one to show what you want in the table (and MSWord).

=if(isna(a17),"",a17)

And hide the column that shows the #n/a's.
 
BoRed79 said:
Unfortunately this didnt work, it brought back #N/A which is not ideal as
this shows up in my graphs and also in the tables that i will be exporting
to
word.

Any other ideas anyone??
Click to expand...
....

#N/A does not show up in Excel charts. It's treated as zero in bar, pie and
area charts, and it's ignored in line and scatter charts. How does it not
work in your charts?

As for display, you need to use TWO tables in Excel in situations like this.
One showing nothing (evaluating to "") for missing numeric values, which you
could use for subsequent calculations or exporting to Word. The other would
replace "" with #N/A for charting.

This is the STANDARD approach to doing this in Excel, so it's unlikely
you'll get a different answer from anyone else.
 
I think that I am getting a #N/A as I am also using the formula to bring back
the chart labels, as well as the data (to produce a dynamic top ten list).
 
Back
Top