manipulating cells with symbols and numbers

[colleenshovlin]

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

 

[Guest]

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

 

[colleenshovlin]

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.








- 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

 

[Guest]

hello collin,

you may try this it may fit your need,

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

 

[David Biddulph]

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.

 

[Guest]

Yes, my formulas are always very happy.