Is this weird or what?

[Finance Guru]

Hi There
I am running Excel 2007.

I have 2 columns consisting of 1316 rows. In Column A and Column B the same
figures apply. However the sum of Column A =sum(A11:A1315) = 5,901,195.18 and
Column B =sum(B11:B1315) = 0.

I have tried pressing F9 to recalculate but it doesn't do anything
By way of further information I do have lots of other Lookups,and other
formula in the sheet. The sheet does contain a lot of info.

Is this a memory problem or What - weird !

All help or advice greatfully accepted

 

[Jarek Kujawa]

see if you don't have apostrophes in front of the figures in column B

 

[Finance Guru]

Hi Jarek,

No there isn't any - I colum C I have put A1-b1 through to the end of the
colums and the result is 0 for each row.

 

[Jarek Kujawa]

what happens if you edit any of those formulae and enter it again?
(I understand F9 or SHIFT+F9 does the same for the workbook, but
nevertheless I'd try to edit one formula just to check)

 

[Finance Guru]

Hi Jarek,


Thanks for the response. I think it is in one of the many formulae that
supports the sheet. I have a copy of last months,which I know there were not
any errors, so I have just had to reconstruct this months file from scratch.
I took a temporary copy of the file with the errors,and I will look at it
later( when I have more time ). I have probably messed somewhere - but thanks
for your help so far.
Kind Regards
FinanceGuru

 

[Pete_UK]

It sounds like the values in column B are text values that look like
numbers. When you carry out arithmetic on them (as in A1-B1) then
Excel will convert them to numbers first, but when you just try to SUM
them they evaluate to zero. If you have formulae in those cells (eg
=RIGHT(F1,2) ) then you can convert them to numbers by adding zero or
multiplying by 1 (i.e. =RIGHT(F1,2)*1 ).

Hope this helps.

Pete

 

[Finance Guru]

Hi Pete+ Jarek,

Thanks for the reply.

I have reconstucted the file( 1.5 hours ) and it is now working fine.
I now think that it was in one of the formulas within the sheet,which I
erased and have copied in again.

As I said previously when I have a quieter moment I will have a look at the
formulas within the 'temp' copy which I took.

Thank you both for your assistance.

 

[Pete_UK]

You're welcome.

Pete

Hi Pete+ Jarek,

Thanks for the reply.  

I have reconstucted the file( 1.5 hours ) and it is now working fine.
I now think that it was in one of the formulas within the sheet,which I
erased and have copied in again.

As I said previously when I have a quieter moment I will have a look at the
formulas within the 'temp' copy which I took.

Thank you both for your assistance.

--
Sometimes common sense just isn't enough - you have to have the knowledge..






- Show quoted text -

 

[Jarek Kujawa]

welcome ;-)