# Number Format Error - This is killing me!

#### loosenut

##### New Member
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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### north19701

##### Active Member
Did you check and make sure the values in A1 and A2 are formatted as number?

#### loosenut

##### New Member
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.

#### Andrew Poulsom

##### MrExcel MVP
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.

#### loosenut

##### New Member

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

#### Yogi Anand

##### MrExcel MVP
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))

#### loosenut

##### New Member
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

#### Andrew Poulsom

##### MrExcel MVP
Data|Text to Columns should do it.

Replies
0
Views
133
Replies
3
Views
699
Replies
11
Views
1K
Replies
1
Views
200
Replies
2
Views
260

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,560
Messages
5,765,091
Members
425,258
Latest member
brentmitchell

### 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.

### Which adblocker are you using?

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

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