|
|
Hello:
I would like to know how to retain the value thats in a cell but delete the
formatting of the cell. Below is a column of numbers that represent response
times (in seconds). I've used special paste to retain the values and reformat
the values to number or general (under formatting), but to no avail. I want
to see what response times exceed our 8min 59sec (or 539 seconds). To do this
I wrote a logic statement, in a seperate column, to mark those times that
meet and/or exceed our response times. The logic statement is IF(A2<=539,
1(true), 2(false)). when I copy the formula down the column all I get is
1(true); even for the values that are obviously false. Not to be out done, in
a seperate column I typed in a few of the values and tried my logic statement
again and it work. I have over 50,000 lines of data and I can't type all the
values in. What else can I do to retain the number value in a cell and yet
delete the formatting of the cell. Again I've tried special paste. This is
what my column of values look like:
Response time(sec)
245
476
345
39
244
139
162
355
311
155
368
266
898
209
Thank in advance for your help.
|
|
Solomon.
Put a 1 in a cell and copy it. Select all your values and then
Edit|Paste Special
select multiply and click ok and then see if your formula work.
Mike
"Solomon" wrote:
[Quoted Text] > Hello:
> I would like to know how to retain the value thats in a cell but delete the
> formatting of the cell. Below is a column of numbers that represent response
> times (in seconds). I've used special paste to retain the values and reformat
> the values to number or general (under formatting), but to no avail. I want
> to see what response times exceed our 8min 59sec (or 539 seconds). To do this
> I wrote a logic statement, in a seperate column, to mark those times that
> meet and/or exceed our response times. The logic statement is IF(A2<=539,
> 1(true), 2(false)). when I copy the formula down the column all I get is
> 1(true); even for the values that are obviously false. Not to be out done, in
> a seperate column I typed in a few of the values and tried my logic statement
> again and it work. I have over 50,000 lines of data and I can't type all the
> values in. What else can I do to retain the number value in a cell and yet
> delete the formatting of the cell. Again I've tried special paste. This is
> what my column of values look like:
>
> Response time(sec)
> 245
> 476
> 345
> 39
> 244
> 139
> 162
> 355
> 311
> 155
> 368
> 266
> 898
> 209
>
> Thank in advance for your help.
|
|
Thanks Mike:
I don't quite understand what U mean by UR reply. Do U mean copy a column of
1's in a column next to the values, then try to special paste, and then try
the logic statement?
"Mike H" wrote:
[Quoted Text] > Solomon.
>
> Put a 1 in a cell and copy it. Select all your values and then
>
> Edit|Paste Special
> select multiply and click ok and then see if your formula work.
>
> Mike
>
> "Solomon" wrote:
>
> > Hello:
> > I would like to know how to retain the value thats in a cell but delete the
> > formatting of the cell. Below is a column of numbers that represent response
> > times (in seconds). I've used special paste to retain the values and reformat
> > the values to number or general (under formatting), but to no avail. I want
> > to see what response times exceed our 8min 59sec (or 539 seconds). To do this
> > I wrote a logic statement, in a seperate column, to mark those times that
> > meet and/or exceed our response times. The logic statement is IF(A2<=539,
> > 1(true), 2(false)). when I copy the formula down the column all I get is
> > 1(true); even for the values that are obviously false. Not to be out done, in
> > a seperate column I typed in a few of the values and tried my logic statement
> > again and it work. I have over 50,000 lines of data and I can't type all the
> > values in. What else can I do to retain the number value in a cell and yet
> > delete the formatting of the cell. Again I've tried special paste. This is
> > what my column of values look like:
> >
> > Response time(sec)
> > 245
> > 476
> > 345
> > 39
> > 244
> > 139
> > 162
> > 355
> > 311
> > 155
> > 368
> > 266
> > 898
> > 209
> >
> > Thank in advance for your help.
|
|
When Mike said "Put a 1 in a cell", by "a cell" he meant *one* cell, not a
column of cells.
Try doing it as Mike said:
"Put a 1 in a cell and copy it. Select all your values and then
Edit|Paste Special
select multiply and click ok and then see if your formula work."
--
David Biddulph
Solomon wrote:
[Quoted Text] > Thanks Mike:
> I don't quite understand what U mean by UR reply. Do U mean copy a
> column of 1's in a column next to the values, then try to special
> paste, and then try the logic statement?
>
> "Mike H" wrote:
>
>> Solomon.
>>
>> Put a 1 in a cell and copy it. Select all your values and then
>>
>> Edit|Paste Special
>> select multiply and click ok and then see if your formula work.
>>
>> Mike
>>
>> "Solomon" wrote:
>>
>>> Hello:
>>> I would like to know how to retain the value thats in a cell but
>>> delete the formatting of the cell. Below is a column of numbers
>>> that represent response times (in seconds). I've used special paste
>>> to retain the values and reformat the values to number or general
>>> (under formatting), but to no avail. I want to see what response
>>> times exceed our 8min 59sec (or 539 seconds). To do this I wrote a
>>> logic statement, in a seperate column, to mark those times that
>>> meet and/or exceed our response times. The logic statement is
>>> IF(A2<=539, 1(true), 2(false)). when I copy the formula down the
>>> column all I get is 1(true); even for the values that are obviously
>>> false. Not to be out done, in a seperate column I typed in a few of
>>> the values and tried my logic statement again and it work. I have
>>> over 50,000 lines of data and I can't type all the values in. What
>>> else can I do to retain the number value in a cell and yet delete
>>> the formatting of the cell. Again I've tried special paste. This is
>>> what my column of values look like:
>>>
>>> Response time(sec)
>>> 245
>>> 476
>>> 345
>>> 39
>>> 244
>>> 139
>>> 162
>>> 355
>>> 311
>>> 155
>>> 368
>>> 266
>>> 898
>>> 209
>>>
>>> Thank in advance for your help.
|
|
No,
Put a 1 in any old cell and then copy the one with right click and copy.
then select your column of data and then
edit|Paste special
click multiply
click ok
delete the one from the cell.
Mike
"Solomon" wrote:
[Quoted Text] > Thanks Mike:
> I don't quite understand what U mean by UR reply. Do U mean copy a column of
> 1's in a column next to the values, then try to special paste, and then try
> the logic statement?
>
> "Mike H" wrote:
>
> > Solomon.
> >
> > Put a 1 in a cell and copy it. Select all your values and then
> >
> > Edit|Paste Special
> > select multiply and click ok and then see if your formula work.
> >
> > Mike
> >
> > "Solomon" wrote:
> >
> > > Hello:
> > > I would like to know how to retain the value thats in a cell but delete the
> > > formatting of the cell. Below is a column of numbers that represent response
> > > times (in seconds). I've used special paste to retain the values and reformat
> > > the values to number or general (under formatting), but to no avail. I want
> > > to see what response times exceed our 8min 59sec (or 539 seconds). To do this
> > > I wrote a logic statement, in a seperate column, to mark those times that
> > > meet and/or exceed our response times. The logic statement is IF(A2<=539,
> > > 1(true), 2(false)). when I copy the formula down the column all I get is
> > > 1(true); even for the values that are obviously false. Not to be out done, in
> > > a seperate column I typed in a few of the values and tried my logic statement
> > > again and it work. I have over 50,000 lines of data and I can't type all the
> > > values in. What else can I do to retain the number value in a cell and yet
> > > delete the formatting of the cell. Again I've tried special paste. This is
> > > what my column of values look like:
> > >
> > > Response time(sec)
> > > 245
> > > 476
> > > 345
> > > 39
> > > 244
> > > 139
> > > 162
> > > 355
> > > 311
> > > 155
> > > 368
> > > 266
> > > 898
> > > 209
> > >
> > > Thank in advance for your help.
|
|
thanks Mike and David. I followed UR instructions to the letter and several
times, but to no avail.
"Mike H" wrote:
[Quoted Text] > No,
>
> Put a 1 in any old cell and then copy the one with right click and copy.
>
> then select your column of data and then
>
> edit|Paste special
>
> click multiply
>
> click ok
>
> delete the one from the cell.
>
> Mike
>
>
> "Solomon" wrote:
>
> > Thanks Mike:
> > I don't quite understand what U mean by UR reply. Do U mean copy a column of
> > 1's in a column next to the values, then try to special paste, and then try
> > the logic statement?
> >
> > "Mike H" wrote:
> >
> > > Solomon.
> > >
> > > Put a 1 in a cell and copy it. Select all your values and then
> > >
> > > Edit|Paste Special
> > > select multiply and click ok and then see if your formula work.
> > >
> > > Mike
> > >
> > > "Solomon" wrote:
> > >
> > > > Hello:
> > > > I would like to know how to retain the value thats in a cell but delete the
> > > > formatting of the cell. Below is a column of numbers that represent response
> > > > times (in seconds). I've used special paste to retain the values and reformat
> > > > the values to number or general (under formatting), but to no avail. I want
> > > > to see what response times exceed our 8min 59sec (or 539 seconds). To do this
> > > > I wrote a logic statement, in a seperate column, to mark those times that
> > > > meet and/or exceed our response times. The logic statement is IF(A2<=539,
> > > > 1(true), 2(false)). when I copy the formula down the column all I get is
> > > > 1(true); even for the values that are obviously false. Not to be out done, in
> > > > a seperate column I typed in a few of the values and tried my logic statement
> > > > again and it work. I have over 50,000 lines of data and I can't type all the
> > > > values in. What else can I do to retain the number value in a cell and yet
> > > > delete the formatting of the cell. Again I've tried special paste. This is
> > > > what my column of values look like:
> > > >
> > > > Response time(sec)
> > > > 245
> > > > 476
> > > > 345
> > > > 39
> > > > 244
> > > > 139
> > > > 162
> > > > 355
> > > > 311
> > > > 155
> > > > 368
> > > > 266
> > > > 898
> > > > 209
> > > >
> > > > Thank in advance for your help.
|
|
Let's start over.
Where did this data come from? Why is there a formatting issue? Check
carefully, are there any Spaces after the numbers in those cells?
Have you tried this:
Edit menu:Clear:Formats
|
|
|