sum() 0.00 result
 

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.

sum() 0.00 result
 

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 editpaste special and select add.
You can also select the column(s), then do datatext to columns and click
finish

--
Regards,

Peo Sjoblom


"Doc d" wrote in message
...
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.

sum() 0.00 result
 

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.

It's because the numbers are seen as text due to the
import/export, no formatting will change that.

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

sum() 0.00 result
 

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.

It's because the numbers are seen as text due to the
import/export, no formatting will change that.

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.

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

sum() 0.00 result
 

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.

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

sum() 0.00 result
 

Hi Rick

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

achieve the desired result.

--
Regards

Roger Govier


"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

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

sum() 0.00 result
 

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

achieve the desired result.

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