Hello, I am having a problem with a formula in an Excel spreadsheet that I
have been using for two years, but for some unknown reason it has stopped
working.

About every ten months I copy this worksheet with all formulas to a new tab
in the workbook. This is the very thing I have done here. The total it
usually derives has not been calculated and has been replaced by the figure
it is meant to subtract and made it a negative value.

I will enclose a copy of the formula here in the hope somebody can see what
it is doing differently, however it is exactly the same formula I have been
using in previous worksheets.

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-VLOOKUP
99999999,B7:B48,1)),0,VLOOKUP(99999999,C7:CB48,1)-VLOOKUP(99999999,B7:B48,1))
--
Steven.

In God we trust, all others we virus scan.

Hi,
Count the parenthese. one more of ) than of (
i think.
Dave
elusiverunner Wrote:
Hello, I am having a problem with a formula in an Excel spreadsheet that
I
have been using for two years, but for some unknown reason it has
stopped
working.

About every ten months I copy this worksheet with all formulas to a new
tab
in the workbook. This is the very thing I have done here. The total
it
usually derives has not been calculated and has been replaced by the
figure
it is meant to subtract and made it a negative value.

I will enclose a copy of the formula here in the hope somebody can see
what
it is doing differently, however it is exactly the same formula I have
been
using in previous worksheets.

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-VLOOKUP
99999999,B7:B48,1)),0,VLOOKUP(99999999,C7:CB48,1)-VLOOKUP(99999999,B7:B48,1))
--
Steven.

In God we trust, all others we virus scan.


--
Desert Piranha
------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=486750

It is a little difficult to tell, the formula in itself works fine, as long
as the data suits the assumptions that the formula was built upon.

When you say it doesn't work, what exactly does that mean, do you get an
error, the wrong answer?

Can you give a data example showing how it doesn't work?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"elusiverunner" wrote in message
...
Hello, I am having a problem with a formula in an Excel spreadsheet that I
have been using for two years, but for some unknown reason it has stopped
working.

About every ten months I copy this worksheet with all formulas to a new
tab
in the workbook. This is the very thing I have done here. The total it
usually derives has not been calculated and has been replaced by the
figure
it is meant to subtract and made it a negative value.

I will enclose a copy of the formula here in the hope somebody can see
what
it is doing differently, however it is exactly the same formula I have
been
using in previous worksheets.

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-VLOOKUP

99999999,B7:B48,1)),0,VLOOKUP(99999999,C7:CB48,1)-VLOOKUP(99999999,B7:B48,1)
)
--
Steven.

In God we trust, all others we virus scan.

BTW, I assumed that as the formula has been okay that the missing ( was a
typo, but you should at least confirm that it was just a typo, else use

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1)),0,
VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
It is a little difficult to tell, the formula in itself works fine, as
long
as the data suits the assumptions that the formula was built upon.

When you say it doesn't work, what exactly does that mean, do you get an
error, the wrong answer?

Can you give a data example showing how it doesn't work?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"elusiverunner" wrote in message
...
Hello, I am having a problem with a formula in an Excel spreadsheet that
I
have been using for two years, but for some unknown reason it has
stopped
working.

About every ten months I copy this worksheet with all formulas to a new
tab
in the workbook. This is the very thing I have done here. The total it
usually derives has not been calculated and has been replaced by the
figure
it is meant to subtract and made it a negative value.

I will enclose a copy of the formula here in the hope somebody can see
what
it is doing differently, however it is exactly the same formula I have
been
using in previous worksheets.

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-VLOOKUP


99999999,B7:B48,1)),0,VLOOKUP(99999999,C7:CB48,1)-VLOOKUP(99999999,B7:B48,1)
)
--
Steven.

In God we trust, all others we virus scan.

Yes it was a typo - I have just seen that I left the bracket out. What I
mean when I say it doesn't work I mean the answer is not what it is supposed
to be. I don't know if I can post the entire work sheet on the forum because
it is rather big.

What the formula is supposed to do is to calculate the distance travelled by
a car since its last service - the answer should have been calculated and
place in the cell that contains this formula. What it placed in the cell was
the last service odometer reading and created a negative value.

As I said in my original posting the formula has always given the correct
answer. I just copy the table and formulas to another worksheet and remove
only the values in the cells not the formula. Even when I copy the formula
by hand rather than just cut and paste it makes no difference to the result.
--
Steven.

In God we trust, all others we virus scan.


"Bob Phillips" wrote:

BTW, I assumed that as the formula has been okay that the missing ( was a
typo, but you should at least confirm that it was just a typo, else use

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1)),0,
VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
It is a little difficult to tell, the formula in itself works fine, as
long
as the data suits the assumptions that the formula was built upon.

When you say it doesn't work, what exactly does that mean, do you get an
error, the wrong answer?

Can you give a data example showing how it doesn't work?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"elusiverunner" wrote in message
...
Hello, I am having a problem with a formula in an Excel spreadsheet that
I
have been using for two years, but for some unknown reason it has
stopped
working.

About every ten months I copy this worksheet with all formulas to a new
tab
in the workbook. This is the very thing I have done here. The total it
usually derives has not been calculated and has been replaced by the
figure
it is meant to subtract and made it a negative value.

I will enclose a copy of the formula here in the hope somebody can see
what
it is doing differently, however it is exactly the same formula I have
been
using in previous worksheets.

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-VLOOKUP


99999999,B7:B48,1)),0,VLOOKUP(99999999,C7:CB48,1)-VLOOKUP(99999999,B7:B48,1)
)
--
Steven.

In God we trust, all others we virus scan.

Can you just give an example of what data you are using, what the answer
should be, and what you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"elusiverunner" wrote in message
...
Yes it was a typo - I have just seen that I left the bracket out. What I
mean when I say it doesn't work I mean the answer is not what it is
supposed
to be. I don't know if I can post the entire work sheet on the forum
because
it is rather big.

What the formula is supposed to do is to calculate the distance travelled
by
a car since its last service - the answer should have been calculated and
place in the cell that contains this formula. What it placed in the cell
was
the last service odometer reading and created a negative value.

As I said in my original posting the formula has always given the correct
answer. I just copy the table and formulas to another worksheet and
remove
only the values in the cells not the formula. Even when I copy the
formula
by hand rather than just cut and paste it makes no difference to the
result.
--
Steven.

In God we trust, all others we virus scan.


"Bob Phillips" wrote:

BTW, I assumed that as the formula has been okay that the missing ( was
a
typo, but you should at least confirm that it was just a typo, else use

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1)),0,
VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
It is a little difficult to tell, the formula in itself works fine, as
long
as the data suits the assumptions that the formula was built upon.

When you say it doesn't work, what exactly does that mean, do you get
an
error, the wrong answer?

Can you give a data example showing how it doesn't work?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"elusiverunner" wrote in
message
...
Hello, I am having a problem with a formula in an Excel spreadsheet
that
I
have been using for two years, but for some unknown reason it has
stopped
working.

About every ten months I copy this worksheet with all formulas to a
new
tab
in the workbook. This is the very thing I have done here. The
total it
usually derives has not been calculated and has been replaced by the
figure
it is meant to subtract and made it a negative value.

I will enclose a copy of the formula here in the hope somebody can
see
what
it is doing differently, however it is exactly the same formula I
have
been
using in previous worksheets.

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-VLOOKUP



99999999,B7:B48,1)),0,VLOOKUP(99999999,C7:CB48,1)-VLOOKUP(99999999,B7:B48,1)
)
--
Steven.

In God we trust, all others we virus scan.

This is no longer a problem. I copied the spreadsheet with just the formulas
to another page. I re-entered all the data and all was OK. Thanks for all
your help.
--
Steven.

In God we trust, all others we virus scan.


"Bob Phillips" wrote:

Can you just give an example of what data you are using, what the answer
should be, and what you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"elusiverunner" wrote in message
...
Yes it was a typo - I have just seen that I left the bracket out. What I
mean when I say it doesn't work I mean the answer is not what it is
supposed
to be. I don't know if I can post the entire work sheet on the forum
because
it is rather big.

What the formula is supposed to do is to calculate the distance travelled
by
a car since its last service - the answer should have been calculated and
place in the cell that contains this formula. What it placed in the cell
was
the last service odometer reading and created a negative value.

As I said in my original posting the formula has always given the correct
answer. I just copy the table and formulas to another worksheet and
remove
only the values in the cells not the formula. Even when I copy the
formula
by hand rather than just cut and paste it makes no difference to the
result.
--
Steven.

In God we trust, all others we virus scan.


"Bob Phillips" wrote:

BTW, I assumed that as the formula has been okay that the missing ( was
a
typo, but you should at least confirm that it was just a typo, else use

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1)),0,
VLOOKUP(99999999,C7:CB48,1)-
VLOOKUP(99999999,B7:B48,1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
It is a little difficult to tell, the formula in itself works fine, as
long
as the data suits the assumptions that the formula was built upon.

When you say it doesn't work, what exactly does that mean, do you get
an
error, the wrong answer?

Can you give a data example showing how it doesn't work?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"elusiverunner" wrote in
message
...
Hello, I am having a problem with a formula in an Excel spreadsheet
that
I
have been using for two years, but for some unknown reason it has
stopped
working.

About every ten months I copy this worksheet with all formulas to a
new
tab
in the workbook. This is the very thing I have done here. The
total it
usually derives has not been calculated and has been replaced by the
figure
it is meant to subtract and made it a negative value.

I will enclose a copy of the formula here in the hope somebody can
see
what
it is doing differently, however it is exactly the same formula I
have
been
using in previous worksheets.

=IF(ISERROR(VLOOKUP(99999999,C7:CB48,1)-VLOOKUP



99999999,B7:B48,1)),0,VLOOKUP(99999999,C7:CB48,1)-VLOOKUP(99999999,B7:B48,1)
)
--
Steven.

In God we trust, all others we virus scan.