Excel math functions not working

[midgardb]

I have a page that displays an Excel table using ASP. Everything seem
to work just fine, but any attempt to sum (or run other math functions
results in a 0 column total. Ideas?

Thanks,
Dav

 

[Ron Rosenfeld]

I have a page that displays an Excel table using ASP. Everything seems
to work just fine, but any attempt to sum (or run other math functions)
results in a 0 column total. Ideas?

Thanks,
Dave

Probably those numbers are really text.

You can check by using the ISTEXT(A1) function.

If they are, convert by placing a 1 in an unused cell. Copy that cell; then
paste special Multiply over the range where the numbers are in your table.

Other problems would include having a non-printing character in the string.
These can be cleaned out by something like

=SUBSTITUTE(TRIM(A1),CHAR(160),"")


--ron

 

[midgardb]

Thanks, Ron. I really appreciate the insight.

Strange thing is all the columns (whether numbers are copied/pasted o
manually inserted) are all default formatted as "General". If I jus
copy out the number/value from the cell and paste it to another cell
can then do math functions on that. If I just copy the cell (with th
value) and paste it to another cell it still doesn't work. It almos
seemed like there was a framework affecting Excel's viewing of th
value.

Non-printing characters might be something, but I'm not seeing wher
that would be coming from. The ASP accesses the data (in SQLServer) an
displays it as an Excel table. Since it's only displaying the recordse
I don't see where anything else would come into play. -I'll kee
chugging at it.

Thanks again,
Dav

 

[Jerry W. Lewis]

Format affects the display, not the value itself. Your data source is
giving you text. You have to convert it to a number before math
functions will work on it. You can do SUM(VALUE(range)) as an array
formula (Ctrl-Shift-Enter) or you can copy a blank cell, select all the
text numbers and Edit|Past Special|Sum

Jerry

 

[Ron Rosenfeld]

Thanks, Ron. I really appreciate the insight.

Strange thing is all the columns (whether numbers are copied/pasted or
manually inserted) are all default formatted as "General". If I just
copy out the number/value from the cell and paste it to another cell I
can then do math functions on that. If I just copy the cell (with the
value) and paste it to another cell it still doesn't work. It almost
seemed like there was a framework affecting Excel's viewing of the
value.

Non-printing characters might be something, but I'm not seeing where
that would be coming from. The ASP accesses the data (in SQLServer) and
displays it as an Excel table. Since it's only displaying the recordset
I don't see where anything else would come into play. -I'll keep
chugging at it.

Thanks again,
Dave

Dave,

I cannot tell from what you've written whether or not you tried my suggestions,
and what happened. Please advise.

By the way, for the issue you are describing, the cell formatting is generally
irrelevant.

As far as your other questions, what do you mean by "copy out the number/value
from the cell and paste it to another cell" vs "just copy the cell (with the
value) and paste it to another cell".


--ron

 

[midgardb]

Ron,
If I just position the cursor over the cell, do right click, copy/past
the cell (and numeric value) the problem remains unchanged. If
instead drag the cursor over just the numeric value in the cell an
copy/paste only the numbers into a new cell math functions then work
Having to individually copy the cell data's not really a solution, so
figured the way each cell held its data was the culprit.

Excel's not something I use often enough to understand the inclusion o
formulas to correct the problem. Since ASP's sending the data/recordse
that populates the Excel tables would it be included on the ASP end? I
it's to be included on the Excel end how would clients benefit sinc
it's not been put to their (client) end as well?

-Don't mean to drag this out.

Thanks again,
Dav

 

[Ron Rosenfeld]

Ron,
If I just position the cursor over the cell, do right click, copy/paste
the cell (and numeric value) the problem remains unchanged. If I
instead drag the cursor over just the numeric value in the cell and
copy/paste only the numbers into a new cell math functions then work.
Having to individually copy the cell data's not really a solution, so I
figured the way each cell held its data was the culprit.

Excel's not something I use often enough to understand the inclusion of
formulas to correct the problem. Since ASP's sending the data/recordset
that populates the Excel tables would it be included on the ASP end? If
it's to be included on the Excel end how would clients benefit since
it's not been put to their (client) end as well?

-Don't mean to drag this out.

Thanks again,
Dave


---

Dave,

From what you've written, including the above, it's clear that the data is
coming into Excel as text. The SUM function, as well as other functions,
interpret textual representations of numbers as having a numeric value of '0'.

If you want to use these math functions in Excel, you are going to have to
either convert them in Excel, or convert them when you import them into Excel,
or convert them before you import them into Excel.

If you are going to convert them in Excel or during the import, you are going
to have to furnish your clients with the appropriate tools. The only
alternative would be to convert the data before your clients get at it, so it
is imported into Excel as numbers.

While there are a number of different methods that I know can be applied in
Excel, I don't know anything about generating the data with ASP and so cannot
help you there -- except that we have more clearly defined the problem.


--ron