I don't Understand

[Guest]

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

 

[Desert Piranha]

Hi,
Count the parenthese. one more of ) than of (
i think.

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

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)

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)

 

[Guest]

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.

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]

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)

 

[Guest]

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.