manipulating cells with symbols and numbers
 

Does anyone know if I can do this...

I have a column of values that I would like to find the average of,
but
some of the cells contain numbers with a < in front of them.
The average should use 1/2 the value of the numbers with < in front of
them.

Thanks in advance for your help!

Colleen

manipulating cells with symbols and numbers
 

You would need to create a helper column which converts the cell value into
the appropriate numerical value.

Assume your data are in A1:A100.

Then, in B1 enter the following: =IF(LEFT(A1,1)="",MID(A1,2,1000)/2,A1) and
fill down. Then average the values in B1:B100.

Note the formula assumes that (1) there is no space or other character to
the left of the symbol and (2) there is no space or other non-number
character to the right of the symbol and the first digit of your number and
(3) the longest digit you have is 1000 digits long.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


" wrote:

Does anyone know if I can do this...

I have a column of values that I would like to find the average of,
but
some of the cells contain numbers with a < in front of them.
The average should use 1/2 the value of the numbers with < in front of
them.

Thanks in advance for your help!

Colleen

manipulating cells with symbols and numbers
 

On Feb 1, 11:41 am, Dave F wrote:
You would need to create a helper column which converts the cell value into
the appropriate numerical value.

Assume your data are in A1:A100.

Then, in B1 enter the following: =IF(LEFT(A1,1)="",MID(A1,2,1000)/2,A1) and
fill down. Then average the values in B1:B100.

Note the formula assumes that (1) there is no space or other character to
the left of the symbol and (2) there is no space or other non-number
character to the right of the symbol and the first digit of your number and
(3) the longest digit you have is 1000 digits long.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.



" wrote:
Does anyone know if I can do this...

I have a column of values that I would like to find the average of,
but
some of the cells contain numbers with a < in front of them.
The average should use 1/2 the value of the numbers with < in front of
them.

Thanks in advance for your help!

Colleen- Hide quoted text -

- Show quoted text -

Using =IF(LEFT(A1,1)="<",MID(A1,2,1000),A1)
I got it to return the value of the cell without the symbol, but
dividing my 2 did not work, and
it will not return the value of A1 if it does not have the symbol in
front.

Thank you for your help, let me know if you have any more ideas!

Colleen

manipulating cells with symbols and numbers
 

hello collin,

you may try this it may fit your need,

=SUM(IF(NOT(ISERROR(FIND("<",A1:A100,1))),(RIGHT(A 1:A100,LEN(A1:A100)-FIND("<",A1:A100,1)))*{1}))*1/2
hit ctrl-shft-ent.
FOR DATA LIKE "<2007" or "KO<2007"
regards
--
*****
birds of the same feather flock together..



" wrote:

Does anyone know if I can do this...

I have a column of values that I would like to find the average of,
but
some of the cells contain numbers with a < in front of them.
The average should use 1/2 the value of the numbers with < in front of
them.

Thanks in advance for your help!

Colleen

manipulating cells with symbols and numbers
 

I suggest that you check the 3 criteria that Dave F gave, as the formula
works quite happily.
Make sure that there are no extraneous spaces either side of the number.
--
David Biddulph

wrote in message
oups.com...

Using =IF(LEFT(A1,1)="<",MID(A1,2,1000),A1)
I got it to return the value of the cell without the symbol, but
dividing my 2 did not work, and
it will not return the value of A1 if it does not have the symbol in
front.

Thank you for your help, let me know if you have any more ideas!

On Feb 1, 11:41 am, Dave F wrote:
You would need to create a helper column which converts the cell value
into
the appropriate numerical value.

Assume your data are in A1:A100.

Then, in B1 enter the following: =IF(LEFT(A1,1)="",MID(A1,2,1000)/2,A1)
and
fill down. Then average the values in B1:B100.

Note the formula assumes that (1) there is no space or other character to
the left of the symbol and (2) there is no space or other non-number
character to the right of the symbol and the first digit of your number
and
(3) the longest digit you have is 1000 digits long.

Dave

" wrote:
Does anyone know if I can do this...

I have a column of values that I would like to find the average of,
but
some of the cells contain numbers with a < in front of them.
The average should use 1/2 the value of the numbers with < in front of
them.

Thanks in advance for your help!

manipulating cells with symbols and numbers
 

Yes, my formulas are always very happy.
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David Biddulph" wrote:

I suggest that you check the 3 criteria that Dave F gave, as the formula
works quite happily.
Make sure that there are no extraneous spaces either side of the number.
--
David Biddulph

wrote in message
oups.com...

Using =IF(LEFT(A1,1)="<",MID(A1,2,1000),A1)
I got it to return the value of the cell without the symbol, but
dividing my 2 did not work, and
it will not return the value of A1 if it does not have the symbol in
front.

Thank you for your help, let me know if you have any more ideas!

On Feb 1, 11:41 am, Dave F wrote:
You would need to create a helper column which converts the cell value
into
the appropriate numerical value.

Assume your data are in A1:A100.

Then, in B1 enter the following: =IF(LEFT(A1,1)="",MID(A1,2,1000)/2,A1)
and
fill down. Then average the values in B1:B100.

Note the formula assumes that (1) there is no space or other character to
the left of the symbol and (2) there is no space or other non-number
character to the right of the symbol and the first digit of your number
and
(3) the longest digit you have is 1000 digits long.

Dave

" wrote:
Does anyone know if I can do this...

I have a column of values that I would like to find the average of,
but
some of the cells contain numbers with a < in front of them.
The average should use 1/2 the value of the numbers with < in front of
them.

Thanks in advance for your help!