Excel 2013 sum function returns 0 for range of nonzero numbers

Question

I'm having problems with what I would hope to be the most robust and simple excel formula. I was given a "#DIV/0" error when trying to calculate the proportion of a cell's content to the sum of the cells in a range (e.g. "=AO35/SUM(AO35:A039)). Cells in the referenced range were formatted as numbers, and I've also tried "general."  Digging into the next layer, excel returns a value of 0 for both "=SUM(AO35:AO39)" and "=SUM(AO35,AO36,AO37,AO38,AO39)" which have values of 3, 41, 1, 3, 15. Now if I enter the formula "=AO35+AO36+AO37+AO38+AO39" excel returns the correct value of 63. Am I missing something here? because I have long been under the impression that the sum of a set of numbers is obtained by adding them together. If this is false then my whole mathematical world of calculating SD, SEM, residuals, etc. is about to come crashing down.

The one and only factor that I can think of which could be effecting this (assuming that the coding for excel could possibly be flawed) is that I had copied the worksheet in question into an existing workbook while retaining the original theme (to retain cell colors). Both were created in the same version of excel though. Again, I have checked the cell formatting for the range of cells, as well as for the formula cell.

Any help, advice, or insight is greatly appreciated.

~D

Answer 1

Your numbers are stored as a Text in your range. See my snip shot, where Column E is my different formula while column F is the formula Text. When you sum individually with "equal to" sign excel smartly recognizes those text numbers and convert it in to number for solution.

To solve it, you have to select your range and then you will see a small yellow pop in right corner, click it and from there select convert to number.

Answer 2

Most functions ignore Text, including number stored as text.  SUM is one of them.

For your reference:

Sum vs. +

Answer 3

1. Copy a blank cell.
2. Select the cells or the entire column where values are in text.
3. Paste Special > Add

Your all text numbers will be converted into numbers and non-numbers will be left untouched.

Answer 4

I'm having problems with what I would hope to be the most robust and simple excel formula. I was given a "#DIV/0" error when trying to calculate the proportion of a cell's content to the sum of the cells in a range (e.g. "=AO35/SUM(AO35:A039)). Cells in the referenced range were formatted as numbers, and I've also tried "general."  Digging into the next layer, excel returns a value of 0 for both "=SUM(AO35:AO39)" and "=SUM(AO35,AO36,AO37,AO38,AO39)" which have values of 3, 41, 1, 3, 15. Now if I enter the formula "=AO35+AO36+AO37+AO38+AO39" excel returns the correct value of 63. Am I missing something here? because I have long been under the impression that the sum of a set of numbers is obtained by adding them together. If this is false then my whole mathematical world of calculating SD, SEM, residuals, etc. is about to come crashing down.

The one and only factor that I can think of which could be effecting this (assuming that the coding for excel could possibly be flawed) is that I had copied the worksheet in question into an existing workbook while retaining the original theme (to retain cell colors). Both were created in the same version of excel though. Again, I have checked the cell formatting for the range of cells, as well as for the formula cell.

Any help, advice, or insight is greatly appreciated.

~D

This is a GREAT & SIMPLE post.

The Context of my problem was values imported from a web site, then subsequently processed in a VLOOKUP function.

I tried MANY things...  I could not eliminate my errors .

I dont know WHY I had such trouble with this, but THANKS FOR THIS SIMPLE but powerful, problem solving tip.

BG