Cell Format

[arrowhead42]

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.

 

[VoG]

Try formatting the cell as General then re-enter the formula.

 

[MrKowz]

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]

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]

What does this return?

=ISNUMBER(B2)

 

[MrKowz]

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]

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

 

[MrKowz]

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]

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]

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:

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