Can't do calculations with the data from a web queries

M

Martin

I get to do the queries, the data is there and refresh and all, but I can't
do a simple calculation with the data. What am I doing wrong? cell format?
 
S

Sheila D

You haven't given much info but maybe you have formatted your numbers as Text
in which case formulaes won't work
 
O

Otto Moehrbach

You don't give much information. Are the cells formatted as Text?. If they
are, do this. In some out-of-the-way cell, enter "1" without the quotes.
Then, with that cell selected, do Edit - Copy. Now select all the cells
that have your non-numbers. Now do Edit - PasteSpecial and click on the
Multiply box. Click OK. Does that do it?
If that doesn't work, what are you trying to do when you do "simple
calculation" and exactly how are you doing it? HTH Otto
 
M

Martin

alright, my excel page is in accounting format as far as the cells are
concerned. When I do my web query, I import the data, here's what it looks
quickly

Stock price dividend
AFLA 15.00 152$
AGDC 10.00 1598$

now, imagine 15 rows like that. I would like to make a sum of the dividend
column. When I import it, the data I want is all showing, but I just can't
do anything with beside refresh it.

When I do the query, the options selected are:
- Formatting: none
- Import settings for preformatted <PRE> blocks : first 2 options are selected
- Other Import settings: none selected
then I import.

"Sheila D" a écrit :
 
O

Otto Moehrbach

Martin
Excel does recognize xxx$ as a number (x is a number). You will have to
remove the $ sign. Say that the dive column is Column C and the data starts
in C2. In some empty column, in row 2, type this formula:
=LEFT(C2,LEN(C2)-1)

Now drag that formula down as far as the data in Column C goes.

Select all the used cells in that new column.

Do Edit - Copy

Select C2.

Do Edit - Paste Special - Values

Delete all the data in the new column.

Done.

HTH Otto
 
M

Martin

Thanks Otto,

When I type the formula you gave, it says it contains an error.

I did it in H2, replacing the C2 from your formula for D2, since this is
where the data I want to work with is...I am getting desperate :)

"Otto Moehrbach" a écrit :
 
O

Otto Moehrbach

Martin
This is too simple to have an error. The formula should look like this
after changing my C to your D:
=LEFT(D2,LEN(D2)-1).
If you still get an error, send me your file or a file that has that portion
of your file. My email address is [email protected]. Remove the "nop"
from this address. HTH Otto
 
Top