sum() 0.00 result

D

Doc d

A report was exported to Excel. When using =sum(start:end) on a numerical
column the result is 0.00. But if use =cell1+cell2+cell3 the result is okay.

Tried using format as numerical, currency, and accounting to no avail.
 
P

Peo Sjoblom

It's because the numbers are seen as text due to the import/export, no
formatting will change that. A couple of ways, select an empty cell, format
the cell the same way you want all the imported numbers, copy the cell,
select all imported numbers and do edit>paste special and select add.
You can also select the column(s), then do data>text to columns and click
finish
 
R

Rick Rothstein \(MVP - VB\)

A report was exported to Excel. When using =sum(start:end)
It's because the numbers are seen as text due to the
import/export, no formatting will change that.
Click to expand...

I'm still too new with Excel to be sure of my answers; but, in my test with
numbers entered into cells as text, the following array formula

=SUM(--TEXT(A4:A8,"#."&REPT("#",15)))

entered using Ctrl+Shift+<Enter> seemed to work. Obviously, if you knew a
maximum number of decimal places, you could dump the REPT function call and
simply specify it.

Rick
 
R

Rick Rothstein \(MVP - VB\)

A report was exported to Excel. When using =sum(start:end)
I'm still too new with Excel to be sure of my answers; but, in my test
with numbers entered into cells as text, the following array formula

=SUM(--TEXT(A4:A8,"#."&REPT("#",15)))

entered using Ctrl+Shift+<Enter> seemed to work. Obviously, if you knew a
maximum number of decimal places, you could dump the REPT function call
and simply specify it.
Click to expand...

I was thinking... assuming I am right and the array formula I posted is a
valid solution to the problem, there is probably no reason to use the REPT
function call... simply specifying 15 # signs after the decimal point would
make more sense. I was originally going to try and add only as many # signs
as the longest decimal and then decided that wasn't necessary... then, in my
haste, I simply plopped in a 15 into the REPT function instead of thinking
about what that meant). So, for the record, the array formula I am proposing
is

=SUM(--TEXT(A4:A8, "#.###############"))

again, entered by using Ctrl+Shift+<Enter>. Of course the above will fail if
the range of cells contains an empty cell. If you have to cater to that
possibility, it looks like this array formula could be used instead...

=SUM(--("0"&TEXT(A4:A8, "#.###############")))

again, entered by using Ctrl+Shift+<Enter>.

Rick
 
R

Rick Rothstein \(MVP - VB\)

Of course the above will fail if the range of cells contains an
empty cell. If you have to cater to that possibility, it looks
like this array formula could be used instead...

=SUM(--("0"&TEXT(A4:A8, "#.###############")))

again, entered by using Ctrl+Shift+<Enter>.
Click to expand...

Okay, sometimes I'm an idiot.<g> There is no need to concatenate zero in
front of the TEXT function... we can just use its format string to do that
for us (and save six keystrokes in the process). Here is my **final**
version of the array formula...

=SUM(--TEXT(start:end, "0.###############"))

again, entered by using Ctrl+Shift+<Enter>. (Also note reintroduction of
'start' and 'end' for the cells making up the range... in my last posting, I
accidentally left in my test range instead of generalizing the range.)

Rick
 
R

Roger Govier

Hi Rick

Perhaps I'm missing something here, but wouldn't the array formula
{=SUM(--(start:end))}

achieve the desired result.
 
R

Rick Rothstein \(MVP - VB\)

Perhaps I'm missing something here, but wouldn't the array formula
{=SUM(--(start:end))}

achieve the desired result.
Click to expand...

Nope, you are not missing anything. I am returning to Excel after a long
absence (last played with Excel in the early to mid 1990's) and things have
changed a lot since then... I am trying to get up to speed on all the new
things added since then. As for your array formula (which works fine)... I
thought I tried that and watched it not work (hence, my foray into what I
eventually posted)... obviously I was wrong and must have tried something
that looked similar to it. Anyway, thanks for noting that for me.

Rick
 
Top