Cell Format

arrowhead42

New Member
Joined
Jun 11, 2007
Messages
34
I know this must be something very simple, such as the cell format or something, but I can't figure it out.
When I type in

=sum(b2:e2)

That's exactly what shows up. The formula doesn't calculate anything. I think the cell may be formatted to show everything as text, but if so, what format do I change it to, in order for it to calculate the values? Scientific? Currency? Time? I've tried them all, and nothing makes the formula work.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try formatting the cell as General then re-enter the formula.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Your cell was formatted as text when you entered the formula. Change the cell format to general, then go back into the formula bar and hit enter.
 

arrowhead42

New Member
Joined
Jun 11, 2007
Messages
34
OK, well I tried that, and now it's calculating, but it's wrong.
Here are the values for the cells, and the sum it's coming up with

B2 C2 D2 E2 total

44 49 36 64 0 <--- this is where I have the formula =sum(b2:e2)

Why would it show a total of zero? It should come out to 193.

I don't understand why it's calculating now, but it's wrong.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

What does this return?

=ISNUMBER(B2)
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Those cells might have gotten stored as text too. To check if they are stored as a numerical value, try:

=ISNUMBER(B2)

What did that return?
 

arrowhead42

New Member
Joined
Jun 11, 2007
Messages
34

ADVERTISEMENT

I input isnumber(b2) and it now says "FALSE"
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ok - the "numbers" you have in your spreadsheet are actually stored as text. (Right-click on their cells, go to format, and you will likely see the number format as "text")

How are these numbers generated? Do you manually type them in? Are they the result of a formula? Are they imported from somewhere else?
 

arrowhead42

New Member
Joined
Jun 11, 2007
Messages
34
MrKowz, when you said that, about them being stored as text, it got me to thinking, and so I checked and you're right.... all the cells it's trying to sum up are in text format. I changed them to general format, re-entered the values and =sum(b2:e2) now works right.
Ugh.....
But this means I'll have to do this for every value that isn't working right across my whole spreadsheet.

Aaaaaooooooaaaaooooohhhhh!!! (<------ that's the sound of me wailing loudly!)

Thanks for helping me straighten this out.... now I can continue on with my statistics class.
thanks again -

Steve
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
We can use a macro to speed up this process.

Run this macro on your worksheet. Be sure to back up your worksheet prior to running:

Code:
Public Sub FixNumberFormat()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In ActiveSheet.UsedRange
    With rng
        If .Formula = .Value Then
            .NumberFormat = "General"
            .Value = .Value
        End If
    End With
Next rng
Application.ScreenUpdating = True
End Sub
 

Forum statistics

Threads
1,141,284
Messages
5,705,508
Members
421,399
Latest member
hjweiss00

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