Offset and If Statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BoRed79 - 05 Jul 2007 11:02 GMT

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?

Reply to this Message

Barb Reinhardt - 05 Jul 2007 11:32 GMT

How about something like this?

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

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

HTH,
Barb Reinhardt

> Hi All.
>
[quoted text clipped - 16 lines]
>
> Does anyone know how to do this?

Reply to this Message

BoRed79 - 06 Jul 2007 14:10 GMT

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.

> How about something like this?
>
[quoted text clipped - 25 lines]
> >
> > Does anyone know how to do this?

Reply to this Message

Dave Peterson - 06 Jul 2007 15:17 GMT

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.

> 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
[quoted text clipped - 33 lines]
> > >
> > > Does anyone know how to do this?

Signature

Dave Peterson

Reply to this Message

BoRed79 - 09 Jul 2007 08:08 GMT

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).

> Are you usually bringing a number to be used in the chart--or is this a label
> for the chart.
[quoted text clipped - 48 lines]
> > > >
> > > > Does anyone know how to do this?

Reply to this Message

Harlan Grove - 06 Jul 2007 15:30 GMT

"BoRed79" <BoRed79@discussions.microsoft.com> wrote...

>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??

...

#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.

Reply to this Message

Rate this thread: