|
Frequency of numbers in a range
Hi, I have a column (B) of four figure numbers, to one decimal place. I would
like to know : 1) What is the longest NON occurence of each of the digits 0-9 throughout the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17, and this was the longest non occurence of this number , then 2 = 17 As the column gets added to with a single entry on a daily basis, I'd then like to know 2) How long is it since each digit 0-9 last occured. I will want to set both up as a dynamic range, but I beleive I can do that part! I beleive the function FREQUENCY may do this, but I'm not sure how to set it up to read the decimal place only. The other values are irrelevant. I beleive from previous advice that ".0" may be particularly difficult and may have to be handled differently to 1-9 ? Any assistance much appreciated. |
Frequency of numbers in a range
Hi Graham:
First, re-consider your definition of gap. The posting indicated the row difference plus one. If the value appears in two consecutive rows, that would say the gap is two. Perhaps you meant minus one ( if the gap represents the number of €śempty€ť cells between €śfilled€ť cells) Suppose B1 thru B20 contains: 8329.0 1099.6 7823.4 3691.1 2699.5 3721.7 9334.5 7568.0 7290.4 6878.9 1665.1 5981.7 1297.3 9236.6 7518.2 2834.5 5186.4 4901.1 6344.4 2162.5 It really does not matter how many digits or decimal places there are. In C1 enter: =IF(ISERROR(SEARCH("2",TEXT(B1,"0.0"))),"",ROW()) and copy down In D2 enter: =IF(C2="","",-1+C2-MAX($C$1:C1)) and copy down Leave D1 empty. Make the -1 into +1 if you still want your definition of €śgap€ť Here is B1 thru D20: 8329.0 1 1099.6 7823.4 3 1 3691.1 2699.5 5 1 3721.7 6 0 9334.5 7568.0 7290.4 9 2 6878.9 1665.1 5981.7 1297.3 13 3 9236.6 14 0 7518.2 15 0 2834.5 16 0 5186.4 4901.1 6344.4 2162.5 20 3 The values in column C are just row numbers of the "good" cells. The values in column D are the differences between occurances ("minus one" definition) Finally in an un-used cell: =MAX(D:D) Have a pleasant day! -- Gary''s Student - gsnu200746 "Graham" wrote: Hi, I have a column (B) of four figure numbers, to one decimal place. I would like to know : 1) What is the longest NON occurence of each of the digits 0-9 throughout the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17, and this was the longest non occurence of this number , then 2 = 17 As the column gets added to with a single entry on a daily basis, I'd then like to know 2) How long is it since each digit 0-9 last occured. I will want to set both up as a dynamic range, but I beleive I can do that part! I beleive the function FREQUENCY may do this, but I'm not sure how to set it up to read the decimal place only. The other values are irrelevant. I beleive from previous advice that ".0" may be particularly difficult and may have to be handled differently to 1-9 ? Any assistance much appreciated. |
Frequency of numbers in a range
Hi GS, Many thanks for your reply. I often confuse myself on the "gap". In
this case I would want to count the longest occurence of "4" as being 8. i.e. from B9:B17.(Using your data column) In the case of the same digit appearing consecutively, I would want to count this as 1. I'll experiment to see which gives me the correct outcome. I had hoped to create a table along the lines of: X Y Z 0 12 12 1 6 2 2 5 5 3 7 7 4 6 1 5 9 0 6 10 6 7 8 8 8 20 20 9 10 10 Where column X is the digit, Column Y is the longest non-occurence, or gap, and Column Z is the gap since it's most recent occurence. (Obviously as this is a short column of data, Y & Z are often the same value!) I will work with your suggestions and see where i get with it. Regards Graham "Gary''s Student" wrote: Hi Graham: First, re-consider your definition of gap. The posting indicated the row difference plus one. If the value appears in two consecutive rows, that would say the gap is two. Perhaps you meant minus one ( if the gap represents the number of €śempty€ť cells between €śfilled€ť cells) Suppose B1 thru B20 contains: 8329.0 1099.6 7823.4 3691.1 2699.5 3721.7 9334.5 7568.0 7290.4 6878.9 1665.1 5981.7 1297.3 9236.6 7518.2 2834.5 5186.4 4901.1 6344.4 2162.5 It really does not matter how many digits or decimal places there are. In C1 enter: =IF(ISERROR(SEARCH("2",TEXT(B1,"0.0"))),"",ROW()) and copy down In D2 enter: =IF(C2="","",-1+C2-MAX($C$1:C1)) and copy down Leave D1 empty. Make the -1 into +1 if you still want your definition of €śgap€ť Here is B1 thru D20: 8329.0 1 1099.6 7823.4 3 1 3691.1 2699.5 5 1 3721.7 6 0 9334.5 7568.0 7290.4 9 2 6878.9 1665.1 5981.7 1297.3 13 3 9236.6 14 0 7518.2 15 0 2834.5 16 0 5186.4 4901.1 6344.4 2162.5 20 3 The values in column C are just row numbers of the "good" cells. The values in column D are the differences between occurances ("minus one" definition) Finally in an un-used cell: =MAX(D:D) Have a pleasant day! -- Gary''s Student - gsnu200746 "Graham" wrote: Hi, I have a column (B) of four figure numbers, to one decimal place. I would like to know : 1) What is the longest NON occurence of each of the digits 0-9 throughout the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17, and this was the longest non occurence of this number , then 2 = 17 As the column gets added to with a single entry on a daily basis, I'd then like to know 2) How long is it since each digit 0-9 last occured. I will want to set both up as a dynamic range, but I beleive I can do that part! I beleive the function FREQUENCY may do this, but I'm not sure how to set it up to read the decimal place only. The other values are irrelevant. I beleive from previous advice that ".0" may be particularly difficult and may have to be handled differently to 1-9 ? Any assistance much appreciated. |
Frequency of numbers in a range
My apologies, having worked through your solution, I can see that I didn't
make myself very clear. I'm only interested in the occurence of numbers in the first decimal place position. It is the numbers to the left of the decimal place that are irrelevant. "Graham" wrote: Hi GS, Many thanks for your reply. I often confuse myself on the "gap". In this case I would want to count the longest occurence of "4" as being 8. i.e. from B9:B17.(Using your data column) In the case of the same digit appearing consecutively, I would want to count this as 1. I'll experiment to see which gives me the correct outcome. I had hoped to create a table along the lines of: X Y Z 0 12 12 1 6 2 2 5 5 3 7 7 4 6 1 5 9 0 6 10 6 7 8 8 8 20 20 9 10 10 Where column X is the digit, Column Y is the longest non-occurence, or gap, and Column Z is the gap since it's most recent occurence. (Obviously as this is a short column of data, Y & Z are often the same value!) I will work with your suggestions and see where i get with it. Regards Graham "Gary''s Student" wrote: Hi Graham: First, re-consider your definition of gap. The posting indicated the row difference plus one. If the value appears in two consecutive rows, that would say the gap is two. Perhaps you meant minus one ( if the gap represents the number of €śempty€ť cells between €śfilled€ť cells) Suppose B1 thru B20 contains: 8329.0 1099.6 7823.4 3691.1 2699.5 3721.7 9334.5 7568.0 7290.4 6878.9 1665.1 5981.7 1297.3 9236.6 7518.2 2834.5 5186.4 4901.1 6344.4 2162.5 It really does not matter how many digits or decimal places there are. In C1 enter: =IF(ISERROR(SEARCH("2",TEXT(B1,"0.0"))),"",ROW()) and copy down In D2 enter: =IF(C2="","",-1+C2-MAX($C$1:C1)) and copy down Leave D1 empty. Make the -1 into +1 if you still want your definition of €śgap€ť Here is B1 thru D20: 8329.0 1 1099.6 7823.4 3 1 3691.1 2699.5 5 1 3721.7 6 0 9334.5 7568.0 7290.4 9 2 6878.9 1665.1 5981.7 1297.3 13 3 9236.6 14 0 7518.2 15 0 2834.5 16 0 5186.4 4901.1 6344.4 2162.5 20 3 The values in column C are just row numbers of the "good" cells. The values in column D are the differences between occurances ("minus one" definition) Finally in an un-used cell: =MAX(D:D) Have a pleasant day! -- Gary''s Student - gsnu200746 "Graham" wrote: Hi, I have a column (B) of four figure numbers, to one decimal place. I would like to know : 1) What is the longest NON occurence of each of the digits 0-9 throughout the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17, and this was the longest non occurence of this number , then 2 = 17 As the column gets added to with a single entry on a daily basis, I'd then like to know 2) How long is it since each digit 0-9 last occured. I will want to set both up as a dynamic range, but I beleive I can do that part! I beleive the function FREQUENCY may do this, but I'm not sure how to set it up to read the decimal place only. The other values are irrelevant. I beleive from previous advice that ".0" may be particularly difficult and may have to be handled differently to 1-9 ? Any assistance much appreciated. |
Frequency of numbers in a range
Apologies not neceassary.
I will update this posting tomorrow to include only the digit to the immediate right of the decimal point. -- Gary''s Student - gsnu200746 "Graham" wrote: My apologies, having worked through your solution, I can see that I didn't make myself very clear. I'm only interested in the occurence of numbers in the first decimal place position. It is the numbers to the left of the decimal place that are irrelevant. "Graham" wrote: Hi GS, Many thanks for your reply. I often confuse myself on the "gap". In this case I would want to count the longest occurence of "4" as being 8. i.e. from B9:B17.(Using your data column) In the case of the same digit appearing consecutively, I would want to count this as 1. I'll experiment to see which gives me the correct outcome. I had hoped to create a table along the lines of: X Y Z 0 12 12 1 6 2 2 5 5 3 7 7 4 6 1 5 9 0 6 10 6 7 8 8 8 20 20 9 10 10 Where column X is the digit, Column Y is the longest non-occurence, or gap, and Column Z is the gap since it's most recent occurence. (Obviously as this is a short column of data, Y & Z are often the same value!) I will work with your suggestions and see where i get with it. Regards Graham "Gary''s Student" wrote: Hi Graham: First, re-consider your definition of gap. The posting indicated the row difference plus one. If the value appears in two consecutive rows, that would say the gap is two. Perhaps you meant minus one ( if the gap represents the number of €śempty€ť cells between €śfilled€ť cells) Suppose B1 thru B20 contains: 8329.0 1099.6 7823.4 3691.1 2699.5 3721.7 9334.5 7568.0 7290.4 6878.9 1665.1 5981.7 1297.3 9236.6 7518.2 2834.5 5186.4 4901.1 6344.4 2162.5 It really does not matter how many digits or decimal places there are. In C1 enter: =IF(ISERROR(SEARCH("2",TEXT(B1,"0.0"))),"",ROW()) and copy down In D2 enter: =IF(C2="","",-1+C2-MAX($C$1:C1)) and copy down Leave D1 empty. Make the -1 into +1 if you still want your definition of €śgap€ť Here is B1 thru D20: 8329.0 1 1099.6 7823.4 3 1 3691.1 2699.5 5 1 3721.7 6 0 9334.5 7568.0 7290.4 9 2 6878.9 1665.1 5981.7 1297.3 13 3 9236.6 14 0 7518.2 15 0 2834.5 16 0 5186.4 4901.1 6344.4 2162.5 20 3 The values in column C are just row numbers of the "good" cells. The values in column D are the differences between occurances ("minus one" definition) Finally in an un-used cell: =MAX(D:D) Have a pleasant day! -- Gary''s Student - gsnu200746 "Graham" wrote: Hi, I have a column (B) of four figure numbers, to one decimal place. I would like to know : 1) What is the longest NON occurence of each of the digits 0-9 throughout the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17, and this was the longest non occurence of this number , then 2 = 17 As the column gets added to with a single entry on a daily basis, I'd then like to know 2) How long is it since each digit 0-9 last occured. I will want to set both up as a dynamic range, but I beleive I can do that part! I beleive the function FREQUENCY may do this, but I'm not sure how to set it up to read the decimal place only. The other values are irrelevant. I beleive from previous advice that ".0" may be particularly difficult and may have to be handled differently to 1-9 ? Any assistance much appreciated. |
Frequency of numbers in a range
Hi Graham: Similar to before except:
In C1 enter: =IF(ISERROR(SEARCH("2",RIGHT(TEXT(B1,"0.0"),1)))," ",ROW()) only look for that single digit to the right of the decimal point In D2 enter: =IF(C2="","",C2-MAX($C$1:C1)) to correct the definition of gap -- Gary''s Student - gsnu200746 "Graham" wrote: My apologies, having worked through your solution, I can see that I didn't make myself very clear. I'm only interested in the occurence of numbers in the first decimal place position. It is the numbers to the left of the decimal place that are irrelevant. "Graham" wrote: Hi GS, Many thanks for your reply. I often confuse myself on the "gap". In this case I would want to count the longest occurence of "4" as being 8. i.e. from B9:B17.(Using your data column) In the case of the same digit appearing consecutively, I would want to count this as 1. I'll experiment to see which gives me the correct outcome. I had hoped to create a table along the lines of: X Y Z 0 12 12 1 6 2 2 5 5 3 7 7 4 6 1 5 9 0 6 10 6 7 8 8 8 20 20 9 10 10 Where column X is the digit, Column Y is the longest non-occurence, or gap, and Column Z is the gap since it's most recent occurence. (Obviously as this is a short column of data, Y & Z are often the same value!) I will work with your suggestions and see where i get with it. Regards Graham "Gary''s Student" wrote: Hi Graham: First, re-consider your definition of gap. The posting indicated the row difference plus one. If the value appears in two consecutive rows, that would say the gap is two. Perhaps you meant minus one ( if the gap represents the number of €śempty€ť cells between €śfilled€ť cells) Suppose B1 thru B20 contains: 8329.0 1099.6 7823.4 3691.1 2699.5 3721.7 9334.5 7568.0 7290.4 6878.9 1665.1 5981.7 1297.3 9236.6 7518.2 2834.5 5186.4 4901.1 6344.4 2162.5 It really does not matter how many digits or decimal places there are. In C1 enter: =IF(ISERROR(SEARCH("2",TEXT(B1,"0.0"))),"",ROW()) and copy down In D2 enter: =IF(C2="","",-1+C2-MAX($C$1:C1)) and copy down Leave D1 empty. Make the -1 into +1 if you still want your definition of €śgap€ť Here is B1 thru D20: 8329.0 1 1099.6 7823.4 3 1 3691.1 2699.5 5 1 3721.7 6 0 9334.5 7568.0 7290.4 9 2 6878.9 1665.1 5981.7 1297.3 13 3 9236.6 14 0 7518.2 15 0 2834.5 16 0 5186.4 4901.1 6344.4 2162.5 20 3 The values in column C are just row numbers of the "good" cells. The values in column D are the differences between occurances ("minus one" definition) Finally in an un-used cell: =MAX(D:D) Have a pleasant day! -- Gary''s Student - gsnu200746 "Graham" wrote: Hi, I have a column (B) of four figure numbers, to one decimal place. I would like to know : 1) What is the longest NON occurence of each of the digits 0-9 throughout the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17, and this was the longest non occurence of this number , then 2 = 17 As the column gets added to with a single entry on a daily basis, I'd then like to know 2) How long is it since each digit 0-9 last occured. I will want to set both up as a dynamic range, but I beleive I can do that part! I beleive the function FREQUENCY may do this, but I'm not sure how to set it up to read the decimal place only. The other values are irrelevant. I beleive from previous advice that ".0" may be particularly difficult and may have to be handled differently to 1-9 ? Any assistance much appreciated. |
Frequency of numbers in a range
Once again many thanks. Simply changing the "2" to "*.2" also apears to work.
I've copied this across for each of the digits and seems to work for all, including "0". It doesn't pick up if the longest gap is actually at the end of the data, but I should now be able to use the columns to find the latest occurence, compare the two, and show the longer, which should overcome that issue. Unless of course you have a more refined way of doing so ? Regards Graham "Gary''s Student" wrote: Hi Graham: Similar to before except: In C1 enter: =IF(ISERROR(SEARCH("2",RIGHT(TEXT(B1,"0.0"),1)))," ",ROW()) only look for that single digit to the right of the decimal point In D2 enter: =IF(C2="","",C2-MAX($C$1:C1)) to correct the definition of gap -- Gary''s Student - gsnu200746 "Graham" wrote: My apologies, having worked through your solution, I can see that I didn't make myself very clear. I'm only interested in the occurence of numbers in the first decimal place position. It is the numbers to the left of the decimal place that are irrelevant. "Graham" wrote: Hi GS, Many thanks for your reply. I often confuse myself on the "gap". In this case I would want to count the longest occurence of "4" as being 8. i.e. from B9:B17.(Using your data column) In the case of the same digit appearing consecutively, I would want to count this as 1. I'll experiment to see which gives me the correct outcome. I had hoped to create a table along the lines of: X Y Z 0 12 12 1 6 2 2 5 5 3 7 7 4 6 1 5 9 0 6 10 6 7 8 8 8 20 20 9 10 10 Where column X is the digit, Column Y is the longest non-occurence, or gap, and Column Z is the gap since it's most recent occurence. (Obviously as this is a short column of data, Y & Z are often the same value!) I will work with your suggestions and see where i get with it. Regards Graham "Gary''s Student" wrote: Hi Graham: First, re-consider your definition of gap. The posting indicated the row difference plus one. If the value appears in two consecutive rows, that would say the gap is two. Perhaps you meant minus one ( if the gap represents the number of €śempty€ť cells between €śfilled€ť cells) Suppose B1 thru B20 contains: 8329.0 1099.6 7823.4 3691.1 2699.5 3721.7 9334.5 7568.0 7290.4 6878.9 1665.1 5981.7 1297.3 9236.6 7518.2 2834.5 5186.4 4901.1 6344.4 2162.5 It really does not matter how many digits or decimal places there are. In C1 enter: =IF(ISERROR(SEARCH("2",TEXT(B1,"0.0"))),"",ROW()) and copy down In D2 enter: =IF(C2="","",-1+C2-MAX($C$1:C1)) and copy down Leave D1 empty. Make the -1 into +1 if you still want your definition of €śgap€ť Here is B1 thru D20: 8329.0 1 1099.6 7823.4 3 1 3691.1 2699.5 5 1 3721.7 6 0 9334.5 7568.0 7290.4 9 2 6878.9 1665.1 5981.7 1297.3 13 3 9236.6 14 0 7518.2 15 0 2834.5 16 0 5186.4 4901.1 6344.4 2162.5 20 3 The values in column C are just row numbers of the "good" cells. The values in column D are the differences between occurances ("minus one" definition) Finally in an un-used cell: =MAX(D:D) Have a pleasant day! -- Gary''s Student - gsnu200746 "Graham" wrote: Hi, I have a column (B) of four figure numbers, to one decimal place. I would like to know : 1) What is the longest NON occurence of each of the digits 0-9 throughout the column e.g. if "2" ocured in B12 and didn't recur till B28, a gap of 17, and this was the longest non occurence of this number , then 2 = 17 As the column gets added to with a single entry on a daily basis, I'd then like to know 2) How long is it since each digit 0-9 last occured. I will want to set both up as a dynamic range, but I beleive I can do that part! I beleive the function FREQUENCY may do this, but I'm not sure how to set it up to read the decimal place only. The other values are irrelevant. I beleive from previous advice that ".0" may be particularly difficult and may have to be handled differently to 1-9 ? Any assistance much appreciated. |
| All times are GMT +1. The time now is 04:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com