Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting numbers | Excel Discussion (Misc queries) | |||
can't change the alignment of numbers in cells | Excel Discussion (Misc queries) | |||
sort alphabebically ignoring preceeding symbols or numbers | Excel Worksheet Functions | |||
Numbers instead of symbols on scatter chart | Charts and Charting in Excel | |||
GET.CELL | Excel Worksheet Functions |