Number Format Error - This is killing me!

loosenut

New Member
Joined
Feb 20, 2004
Messages
41
Ok, I've been using Excel for 15 years and this is killing me! This is the problem:

1. I have multiple copied worksheets in my work book
2. In the initial worksheet I use functions such as count, sum, average and they all work just fine.
3. When I copy them into another worsheet in the same workbook they stop working!!!! For instance:

a1 = 4
a2 = 8
a3 = sum(a1,a2)

In the intial worksheet it yields 12. In the copied worksheets it yields 0! My only clue is that when I press f2 then f9 while highlighting 'a1' in the sum equation it registers 4 in the initial worksheet, and "4" (in quotes) in the copied worksheets. Is this some kind of strange formatting issue?

I'd jump out my window but unfortunately I work only on the 2nd floor and would probably just maim myself. Any help would me much appreciated!

Thanks,

Loosenut o_O
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Still Wondering

Yes they are formatted as numbers though the fact they register in quotes when f2/f9 is pressed makes me wonder.

The other key data I forgot to mention is that if in a3 I insert =a1+a2 rather than using the sum equation, it works.

Thanks for your help!
 
Upvote 0
They must be text entries. The formula:

=A1+A2

coerces them into numbers, but the SUM function ignores text entries.

Make sure the cells are not formatted as Text before you do the copy.
 
Upvote 0
Andrew,

thanks for your thoughts. It must have something to do with the 'copy sheet' functionality turning what were number formats into text. Perhaps it also has something to do with the fact that I am pulling the data from a web query.

I'll keep plugging.

BEst,

loosenut
 
Upvote 0
Hi loosenut:

Welcome to MrExcel Board!

If your numeric entries are entered as text, or may be mixed some as text and some as numbers, you may make use of the EVAL function from MoreFunc Add-in ...

=SUM(EVAL(A1:B1))
 
Upvote 0
Almost there.....

Ahhh! Baby steps.

I discovered that when I import data it comes in with spaces infront of every number. Thus even thought the cell is in a number format, it refuses to sum, etc.

Any simple solution to truncating the spaces?

Best,

Loosenut
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top