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

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

loosenut

New Member
Joined
Feb 20, 2004
Messages
41
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!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Feb 20, 2004
Messages
41

ADVERTISEMENT

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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Feb 20, 2004
Messages
41
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
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
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
Top