Quantcast

Trouble with Calc

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Trouble with Calc

Bill Dillinger
Hello,
I can't seem to manage Calc with even a simple problem. I am trying to
read and work with a spreadsheet made from a .csv file with columns of
numbers. I really have tried to read and work with the HELP file.

First I could not add 4 numbers but with lots of tries found that it
seemed to be because all my numbers ended in .0 and when I did a global
replace of .0 with nothing I could then add numbers. Once I did that I
was able to compute an average and then copy the formula to other places
in the spreadsheet. I first did try formatting the number field but that
didn't seem to help. If someone could tell me why I can't use numbers
ending in .0 I would be interested.

Second my current problem is I cannot find the spread of 4 numbers. I
tried =MAX(C2:C3;C4;C5)-MIN(C2;C3;C4:C5) and it worked! Then when I
copied that formula into a new location to get
=MAX(C6;C7;C8;C9)-MIN(C6;C7;C8;C9) it gives me the incorrect value 0. If
I copy or type the formula into any other cell it will not work.

If anyone knows what my problem is I would appreciate it.

Thank you

Bill Dillinger

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Trouble with Calc

Martin Groenescheij


On 14/05/17 5:22 AM, Bill Dillinger wrote:

> Hello,
> I can't seem to manage Calc with even a simple problem. I am trying to
> read and work with a spreadsheet made from a .csv file with columns of
> numbers. I really have tried to read and work with the HELP file.
>
> First I could not add 4 numbers but with lots of tries found that it
> seemed to be because all my numbers ended in .0 and when I did a
> global replace of .0 with nothing I could then add numbers. Once I did
> that I was able to compute an average and then copy the formula to
> other places in the spreadsheet. I first did try formatting the number
> field but that didn't seem to help. If someone could tell me why I
> can't use numbers ending in .0 I would be interested.

First thing you should check is to see if the number is really a number.
Sometimes when you open a CSV file the numbers
are imported as text. It's good practise to tick the box "Detect special
numbers" when you open or import CSV files.
It's easy to check if a number is imported as text by selecting a cell
look at the top bar if the number is preceded with ` e.g.
`123 it is a text field. Changing the format doesn't help.
You could do two things:
  1 deleting all ` in the cells which doesn't make sense if you have a
lot of numbers to change
  2 start from scratch and check the "Detect special numbers" box

>
> Second my current problem is I cannot find the spread of 4 numbers. I
> tried =MAX(C2:C3;C4;C5)-MIN(C2;C3;C4:C5) and it worked! Then when I
> copied that formula into a new location to get
> =MAX(C6;C7;C8;C9)-MIN(C6;C7;C8;C9) it gives me the incorrect value 0.
> If I copy or type the formula into any other cell it will not work.
>
> If anyone knows what my problem is I would appreciate it.
>
> Thank you
>
> Bill Dillinger
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Trouble with Calc

Bill Dillinger
Hello,
I have received helpful suggestions from Brian and Martin and very much
appreciate them. Thanks for the help and any additional notes that may
yet come in. I realize now my original message was not completely clear
because I did not fully understand my problem. As was suggested by both
my problem was due because the numbers in the .csv file were being
brought in as text not numbers.

One suggestion was to click the "Detect special numbers" option when
opening the file. I did try this and it didn't work.
It was suggested to use the function =VALUE(c2) in a new column in Calc
to convert the text to numbers. When I did this I got Err 502.
Then Brian suggested the problem could be due to leading blanks in the
field. This seems to be the real problem. If I remove the leading blanks
in Calc the text becomes numbers.  Also if I remove the leading blanks
in the .csv file the fields will be input as numbers.

I would be impractical to remove leading blanks manually through out the
file. I know editor questions are not appropriate to this forum but FYI
I will mention what I have learned so far. Using OpenOffice Writer I see
that the fields start with a tab character. I am working on it but have
not found a way to find and replace the tab character in any of the
editors I have access to on my computer.

Still working on it. Thanks for the help.

Bill Dillinger


On 5/15/2017 8:46 AM, Martin Groenescheij wrote:

>
>
> On 14/05/17 5:22 AM, Bill Dillinger wrote:
>> Hello,
>> I can't seem to manage Calc with even a simple problem. I am trying
>> to read and work with a spreadsheet made from a .csv file with
>> columns of numbers. I really have tried to read and work with the
>> HELP file.
>>
>> First I could not add 4 numbers but with lots of tries found that it
>> seemed to be because all my numbers ended in .0 and when I did a
>> global replace of .0 with nothing I could then add numbers. Once I
>> did that I was able to compute an average and then copy the formula
>> to other places in the spreadsheet. I first did try formatting the
>> number field but that didn't seem to help. If someone could tell me
>> why I can't use numbers ending in .0 I would be interested.
>
> First thing you should check is to see if the number is really a
> number. Sometimes when you open a CSV file the numbers
> are imported as text. It's good practise to tick the box "Detect
> special numbers" when you open or import CSV files.
> It's easy to check if a number is imported as text by selecting a cell
> look at the top bar if the number is preceded with ` e.g.
> `123 it is a text field. Changing the format doesn't help.
> You could do two things:
>  1 deleting all ` in the cells which doesn't make sense if you have a
> lot of numbers to change
>  2 start from scratch and check the "Detect special numbers" box
>
>>
>> Second my current problem is I cannot find the spread of 4 numbers. I
>> tried =MAX(C2:C3;C4;C5)-MIN(C2;C3;C4:C5) and it worked! Then when I
>> copied that formula into a new location to get
>> =MAX(C6;C7;C8;C9)-MIN(C6;C7;C8;C9) it gives me the incorrect value 0.
>> If I copy or type the formula into any other cell it will not work.
>>
>> If anyone knows what my problem is I would appreciate it.
>>
>> Thank you
>>
>> Bill Dillinger
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Trouble with Calc

Rory O'Farrell
On Mon, 15 May 2017 10:21:21 -0400
Bill Dillinger <[hidden email]> wrote:

> Hello,
> I have received helpful suggestions from Brian and Martin and very much
> appreciate them. Thanks for the help and any additional notes that may
> yet come in. I realize now my original message was not completely clear
> because I did not fully understand my problem. As was suggested by both
> my problem was due because the numbers in the .csv file were being
> brought in as text not numbers.
>
> One suggestion was to click the "Detect special numbers" option when
> opening the file. I did try this and it didn't work.
> It was suggested to use the function =VALUE(c2) in a new column in Calc
> to convert the text to numbers. When I did this I got Err 502.
> Then Brian suggested the problem could be due to leading blanks in the
> field. This seems to be the real problem. If I remove the leading blanks
> in Calc the text becomes numbers.  Also if I remove the leading blanks
> in the .csv file the fields will be input as numbers.
>
> I would be impractical to remove leading blanks manually through out the
> file. I know editor questions are not appropriate to this forum but FYI
> I will mention what I have learned so far. Using OpenOffice Writer I see
> that the fields start with a tab character. I am working on it but have
> not found a way to find and replace the tab character in any of the
> editors I have access to on my computer.

In OpenOffice Writer use /Edit /Find and Replace.  

In the Find window enter \t
In the Replace window enter whatever you wish to replace it with (in this case, leave it blank).  

Drop down More Options and select Regular expressions, then Replace All.


RoryOF

> Still working on it. Thanks for the help.
>
> Bill Dillinger
>
>
> On 5/15/2017 8:46 AM, Martin Groenescheij wrote:
> >
> >
> > On 14/05/17 5:22 AM, Bill Dillinger wrote:
> >> Hello,
> >> I can't seem to manage Calc with even a simple problem. I am trying
> >> to read and work with a spreadsheet made from a .csv file with
> >> columns of numbers. I really have tried to read and work with the
> >> HELP file.
> >>
> >> First I could not add 4 numbers but with lots of tries found that it
> >> seemed to be because all my numbers ended in .0 and when I did a
> >> global replace of .0 with nothing I could then add numbers. Once I
> >> did that I was able to compute an average and then copy the formula
> >> to other places in the spreadsheet. I first did try formatting the
> >> number field but that didn't seem to help. If someone could tell me
> >> why I can't use numbers ending in .0 I would be interested.
> >
> > First thing you should check is to see if the number is really a
> > number. Sometimes when you open a CSV file the numbers
> > are imported as text. It's good practise to tick the box "Detect
> > special numbers" when you open or import CSV files.
> > It's easy to check if a number is imported as text by selecting a cell
> > look at the top bar if the number is preceded with ` e.g.
> > `123 it is a text field. Changing the format doesn't help.
> > You could do two things:
> >  1 deleting all ` in the cells which doesn't make sense if you have a
> > lot of numbers to change
> >  2 start from scratch and check the "Detect special numbers" box
> >
> >>
> >> Second my current problem is I cannot find the spread of 4 numbers. I
> >> tried =MAX(C2:C3;C4;C5)-MIN(C2;C3;C4:C5) and it worked! Then when I
> >> copied that formula into a new location to get
> >> =MAX(C6;C7;C8;C9)-MIN(C6;C7;C8;C9) it gives me the incorrect value 0.
> >> If I copy or type the formula into any other cell it will not work.
> >>
> >> If anyone knows what my problem is I would appreciate it.
> >>
> >> Thank you
> >>
> >> Bill Dillinger
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: [hidden email]
> >> For additional commands, e-mail: [hidden email]
> >>
> >>
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


--
Rory O'Farrell <[hidden email]>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Trouble with Calc

Brian Barker
In reply to this post by Bill Dillinger
At 10:21 15/05/2017 -0400, Bill Dillinger wrote:
>One suggestion was to click the "Detect special numbers" option when
>opening the file. I did try this and it didn't work.

I think it should. The problem is probably the tab characters that
you have only now referred to.

>It was suggested to use the function =VALUE(c2) in a new column in
>Calc to convert the text to numbers. When I did this I got Err 502.

This will work if, as we thought, you just had spare blanks in the
text value in the cell. But it seems it will not cope with
(invisible) tab characters there.

If your CSV file has tab characters as well as commas as separators -
and indeed also blanks as spacers - then in the Text Import dialogue
you will need to tick both Tab and Comma as well as to tick "Merge
delimiters" under "Separated by" and also to tick "Detect special numbers".

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Trouble with Calc

Alan Pearce
In reply to this post by Bill Dillinger
Hi Bill,
I had a similar problem with CSV i.e. with the apostrophie turning the numbers to text. I simply downloaded the file again and it came in correctly without the apostrophie and the numbers behaved as numbers, not text. I had never had this pronlem before and I don't know what, if anything, I did differently.

Regards,
Alan.

Sent from Samsung tablet

-------- Original message --------
From: Bill Dillinger <[hidden email]>
Date:15/05/2017  15:21  (GMT+00:00)
To: [hidden email],[hidden email],Brian Barker <[hidden email]>
Subject: Re: Trouble with Calc

Hello,
I have received helpful suggestions from Brian and Martin and very much
appreciate them. Thanks for the help and any additional notes that may
yet come in. I realize now my original message was not completely clear
because I did not fully understand my problem. As was suggested by both
my problem was due because the numbers in the .csv file were being
brought in as text not numbers.

One suggestion was to click the "Detect special numbers" option when
opening the file. I did try this and it didn't work.
It was suggested to use the function =VALUE(c2) in a new column in Calc
to convert the text to numbers. When I did this I got Err 502.
Then Brian suggested the problem could be due to leading blanks in the
field. This seems to be the real problem. If I remove the leading blanks
in Calc the text becomes numbers.  Also if I remove the leading blanks
in the .csv file the fields will be input as numbers.

I would be impractical to remove leading blanks manually through out the
file. I know editor questions are not appropriate to this forum but FYI
I will mention what I have learned so far. Using OpenOffice Writer I see
that the fields start with a tab character. I am working on it but have
not found a way to find and replace the tab character in any of the
editors I have access to on my computer.

Still working on it. Thanks for the help.

Bill Dillinger


On 5/15/2017 8:46 AM, Martin Groenescheij wrote:

>
>
> On 14/05/17 5:22 AM, Bill Dillinger wrote:
>> Hello,
>> I can't seem to manage Calc with even a simple problem. I am trying
>> to read and work with a spreadsheet made from a .csv file with
>> columns of numbers. I really have tried to read and work with the
>> HELP file.
>>
>> First I could not add 4 numbers but with lots of tries found that it
>> seemed to be because all my numbers ended in .0 and when I did a
>> global replace of .0 with nothing I could then add numbers. Once I
>> did that I was able to compute an average and then copy the formula
>> to other places in the spreadsheet. I first did try formatting the
>> number field but that didn't seem to help. If someone could tell me
>> why I can't use numbers ending in .0 I would be interested.
>
> First thing you should check is to see if the number is really a
> number. Sometimes when you open a CSV file the numbers
> are imported as text. It's good practise to tick the box "Detect
> special numbers" when you open or import CSV files.
> It's easy to check if a number is imported as text by selecting a cell
> look at the top bar if the number is preceded with ` e.g.
> `123 it is a text field. Changing the format doesn't help.
> You could do two things:
>  1 deleting all ` in the cells which doesn't make sense if you have a
> lot of numbers to change
>  2 start from scratch and check the "Detect special numbers" box
>
>>
>> Second my current problem is I cannot find the spread of 4 numbers. I
>> tried =MAX(C2:C3;C4;C5)-MIN(C2;C3;C4:C5) and it worked! Then when I
>> copied that formula into a new location to get
>> =MAX(C6;C7;C8;C9)-MIN(C6;C7;C8;C9) it gives me the incorrect value 0.
>> If I copy or type the formula into any other cell it will not work.
>>
>> If anyone knows what my problem is I would appreciate it.
>>
>> Thank you
>>
>> Bill Dillinger
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Re: Trouble with Calc

Bill Dillinger
In reply to this post by Brian Barker
Thanks to all who responded! I now have an .ods file I can work with.

After I learned to edit the tabs out of my original .csv file I got a
good result. Now it seems that when I reported that leading blanks in
numeric fields were causing me a problem I was mistaken, it was the
tabs. Brian's suggestion in the final paragraph below eliminates the
step of deleting tabs from the input file and gets me a good result.

Thanks again.

Bill Dillinger

On 5/15/2017 11:03 AM, Brian Barker wrote:

> At 10:21 15/05/2017 -0400, Bill Dillinger wrote:
>> One suggestion was to click the "Detect special numbers" option when
>> opening the file. I did try this and it didn't work.
>
> I think it should. The problem is probably the tab characters that you
> have only now referred to.
>
>> It was suggested to use the function =VALUE(c2) in a new column in
>> Calc to convert the text to numbers. When I did this I got Err 502.
>
> This will work if, as we thought, you just had spare blanks in the
> text value in the cell. But it seems it will not cope with (invisible)
> tab characters there.
>
> If your CSV file has tab characters as well as commas as separators -
> and indeed also blanks as spacers - then in the Text Import dialogue
> you will need to tick both Tab and Comma as well as to tick "Merge
> delimiters" under "Separated by" and also to tick "Detect special
> numbers".
>
> I trust this helps.
>
> Brian Barker
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...