Excel 2003 - Incorrect Calculations

  • Thread starter Thread starter Zack
  • Start date Start date
Z

Zack

I'm having (2) distinct problems in Excel:

1) Copy & pasting formulas displays an incorrect value,
until I 'save' the spreadsheet. It looks like I've
copied and pasted a value (same total appears), but when
I save it correctly calculates the new total (formula
begins working).

2) Using vlookup I'm getting a completely wrong result.
I've checked the formula, and it's simply giving me the
wrong cell as an answer - I use vlookup often and have no
idea why it's not working. For example, it's finding the
wrong 'lookup value.'

I assume this is maybe a memory problem, virus, etc. but
wanted to post here to see if it's within Excel.

Thanks,

Zack
 
Zak

Do you have calculation set to manual? (Tools>Options...Calculation). You
can normally tell this as Excel with have 'calculate' displayed in the
status bar at the bottom. (Most Excel versions calculate on save)

You have not posted the formula you have but if you do not have 'FALSE' as
the last parameter, Excel will take the nearest value up or down depending
on sort order.

Post your formula and we'll take a look.

Also bear in mind 'Lookup values' sometimes look different to what is
actually being stored. This can sometimes make it look like it has a wrong
value.

I don't honestly suspect a virus and certainly these issues are not an
'common' Excel issue

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick,

Calculation was set to manual - I have no idea how it got
changed, but THANK YOU.

I changed the vlookup last argument to 'false' and the
first formula is working correctly now. THX.

However, when I copy/paste this vlookup formula it's not
working on the second value. The formula is: =VLOOKUP
(H6,Power_Tools_Data!A$1:BH$277,32,FALSE). It's
returning #NA now. I inserted the $ to keep the range
correct (aside: why doesn't this range hold when
copying/pasting? I know that previously I've just copied
and pasted with no problem). Anyway, it doesn't work
with or without the $

Thanks again,

Zack
-----Original Message-----
Zak

Do you have calculation set to manual?
Click to expand...
(Tools>Options...Calculation). You
 
Zak

I general it is undesirable to have a relative 'Lookup range'. (The second
argument). In your case you have only made the rows absolute and I expect
that is the issue. Your formula should look like

=VLOOKUP(H6,Power_Tools_Data!$A$1:$BH$277,32,FALSE)

VLOOKUP, or HLOOKUP are not good copy and paste candidates, particularly
across columns for VLOOKUP and rows for HLOOKUP as the third parameter
(offset) does not change anyway.

Hopefully this will fix the issue.

BTW: Power tool data?? I am MD of www.northerntooluk.com in the UK

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick,

Thanks once again. I'll take the advice.

re: power tools. I'm a consultant working at a company
that makes carbon brushes, Morganite, who is a supplier
to Black & Decker, Dewalt, Milwaukee, etc. (other US PT
suppliers). Small world. Cheers.
-----Original Message-----
Zak

I general it is undesirable to have a relative 'Lookup range'. (The second
argument). In your case you have only made the rows absolute and I expect
that is the issue. Your formula should look like

=VLOOKUP(H6,Power_Tools_Data!$A$1:$BH$277,32,FALSE)

VLOOKUP, or HLOOKUP are not good copy and paste candidates, particularly
across columns for VLOOKUP and rows for HLOOKUP as the third parameter
(offset) does not change anyway.

Hopefully this will fix the issue.

BTW: Power tool data?? I am MD of
Click to expand...
www.northerntooluk.com in the UK
 
Back
Top