| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested functions inc. dynamic range
HI
I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
At first blush I see that that you have the following
=SUMPRODUCT(--(ISNUMBER(SEARCH(".2",YourStaticRange)))) =SUMPRODUCT(--(ISNUMBER(SEARCH(YourDynamicRange,".2")))) You may want to switch things around in the equation with the dynamic range. HTH, Barb Reinhardt "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
try:
=SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
Many thanks to you both for your help. Toppers, your solution seems a much
easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
Excel doesn't "see" decimals that terminate with 0.
73.00 In order to display the 0s you'd normally have to format as NUMBER 2 decimal places. But 73.00 is only the *displayed* value. The true underlying value is 73. So, the formula will work for numbers like: 73.09 10.02 0.05 But will not work for numbers like: 73.00 10.00 To count only numbers that terminate with 0: =SUMPRODUCT(--(MOD(range,1)=0)) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Many thanks to you both for your help. Toppers, your solution seems a much easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
Thank you very much, I learn something new every time I come on here!
"T. Valko" wrote: Excel doesn't "see" decimals that terminate with 0. 73.00 In order to display the 0s you'd normally have to format as NUMBER 2 decimal places. But 73.00 is only the *displayed* value. The true underlying value is 73. So, the formula will work for numbers like: 73.09 10.02 0.05 But will not work for numbers like: 73.00 10.00 To count only numbers that terminate with 0: =SUMPRODUCT(--(MOD(range,1)=0)) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Many thanks to you both for your help. Toppers, your solution seems a much easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Graham" wrote in message ... Thank you very much, I learn something new every time I come on here! "T. Valko" wrote: Excel doesn't "see" decimals that terminate with 0. 73.00 In order to display the 0s you'd normally have to format as NUMBER 2 decimal places. But 73.00 is only the *displayed* value. The true underlying value is 73. So, the formula will work for numbers like: 73.09 10.02 0.05 But will not work for numbers like: 73.00 10.00 To count only numbers that terminate with 0: =SUMPRODUCT(--(MOD(range,1)=0)) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Many thanks to you both for your help. Toppers, your solution seems a much easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
Hi All, Having got it all to work, including **.00, I'd now like to do the
same with the occurence of digits in the second decimal place, independant of the first. The following works fine for the digits 1-9, e.g. * *.*7: =SUMPRODUCT(--(ISNUMBER(SEARCH(".?7",INDIRECT("$B$12:B"&A2))))) where I have inserted a "?" instead of the first decimal place, as a 'wildcard'. However I am still having difficulty with the Zero's. The following works fine for **.00, as previously, but wont work for **.30 =SUMPRODUCT(--(MOD(INDIRECT("$B$12:B"&A2),1)=0)) Can anyone amend the above or make alternative suggestions to count the number of Zero's in the second decimal place, where the first decimal place is 1-9 ? Regards Graham "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Graham" wrote in message ... Thank you very much, I learn something new every time I come on here! "T. Valko" wrote: Excel doesn't "see" decimals that terminate with 0. 73.00 In order to display the 0s you'd normally have to format as NUMBER 2 decimal places. But 73.00 is only the *displayed* value. The true underlying value is 73. So, the formula will work for numbers like: 73.09 10.02 0.05 But will not work for numbers like: 73.00 10.00 To count only numbers that terminate with 0: =SUMPRODUCT(--(MOD(range,1)=0)) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Many thanks to you both for your help. Toppers, your solution seems a much easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
Assuming all the numbers in the range are formatted as NUMBER 2 decimal
places: =SUMPRODUCT(--(MOD(rng,1)0),--(RIGHT(TEXT(rng,"0.00"))="0")) Based on this sample the result = 2 7.02 7.30 7.33 7.00 0.50 Those being counted are 7.30 and 0.50. -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi All, Having got it all to work, including **.00, I'd now like to do the same with the occurence of digits in the second decimal place, independant of the first. The following works fine for the digits 1-9, e.g. * *.*7: =SUMPRODUCT(--(ISNUMBER(SEARCH(".?7",INDIRECT("$B$12:B"&A2))))) where I have inserted a "?" instead of the first decimal place, as a 'wildcard'. However I am still having difficulty with the Zero's. The following works fine for **.00, as previously, but wont work for **.30 =SUMPRODUCT(--(MOD(INDIRECT("$B$12:B"&A2),1)=0)) Can anyone amend the above or make alternative suggestions to count the number of Zero's in the second decimal place, where the first decimal place is 1-9 ? Regards Graham "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Graham" wrote in message ... Thank you very much, I learn something new every time I come on here! "T. Valko" wrote: Excel doesn't "see" decimals that terminate with 0. 73.00 In order to display the 0s you'd normally have to format as NUMBER 2 decimal places. But 73.00 is only the *displayed* value. The true underlying value is 73. So, the formula will work for numbers like: 73.09 10.02 0.05 But will not work for numbers like: 73.00 10.00 To count only numbers that terminate with 0: =SUMPRODUCT(--(MOD(range,1)=0)) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Many thanks to you both for your help. Toppers, your solution seems a much easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
I dont know how, but it works a treat. Many Thanks again.
"T. Valko" wrote: Assuming all the numbers in the range are formatted as NUMBER 2 decimal places: =SUMPRODUCT(--(MOD(rng,1)0),--(RIGHT(TEXT(rng,"0.00"))="0")) Based on this sample the result = 2 7.02 7.30 7.33 7.00 0.50 Those being counted are 7.30 and 0.50. -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi All, Having got it all to work, including **.00, I'd now like to do the same with the occurence of digits in the second decimal place, independant of the first. The following works fine for the digits 1-9, e.g. * *.*7: =SUMPRODUCT(--(ISNUMBER(SEARCH(".?7",INDIRECT("$B$12:B"&A2))))) where I have inserted a "?" instead of the first decimal place, as a 'wildcard'. However I am still having difficulty with the Zero's. The following works fine for **.00, as previously, but wont work for **.30 =SUMPRODUCT(--(MOD(INDIRECT("$B$12:B"&A2),1)=0)) Can anyone amend the above or make alternative suggestions to count the number of Zero's in the second decimal place, where the first decimal place is 1-9 ? Regards Graham "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Graham" wrote in message ... Thank you very much, I learn something new every time I come on here! "T. Valko" wrote: Excel doesn't "see" decimals that terminate with 0. 73.00 In order to display the 0s you'd normally have to format as NUMBER 2 decimal places. But 73.00 is only the *displayed* value. The true underlying value is 73. So, the formula will work for numbers like: 73.09 10.02 0.05 But will not work for numbers like: 73.00 10.00 To count only numbers that terminate with 0: =SUMPRODUCT(--(MOD(range,1)=0)) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Many thanks to you both for your help. Toppers, your solution seems a much easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested functions inc. dynamic range
You're welcome!
-- Biff Microsoft Excel MVP "Graham" wrote in message ... I dont know how, but it works a treat. Many Thanks again. "T. Valko" wrote: Assuming all the numbers in the range are formatted as NUMBER 2 decimal places: =SUMPRODUCT(--(MOD(rng,1)0),--(RIGHT(TEXT(rng,"0.00"))="0")) Based on this sample the result = 2 7.02 7.30 7.33 7.00 0.50 Those being counted are 7.30 and 0.50. -- Biff Microsoft Excel MVP "Graham" wrote in message ... Hi All, Having got it all to work, including **.00, I'd now like to do the same with the occurence of digits in the second decimal place, independant of the first. The following works fine for the digits 1-9, e.g. * *.*7: =SUMPRODUCT(--(ISNUMBER(SEARCH(".?7",INDIRECT("$B$12:B"&A2))))) where I have inserted a "?" instead of the first decimal place, as a 'wildcard'. However I am still having difficulty with the Zero's. The following works fine for **.00, as previously, but wont work for **.30 =SUMPRODUCT(--(MOD(INDIRECT("$B$12:B"&A2),1)=0)) Can anyone amend the above or make alternative suggestions to count the number of Zero's in the second decimal place, where the first decimal place is 1-9 ? Regards Graham "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Graham" wrote in message ... Thank you very much, I learn something new every time I come on here! "T. Valko" wrote: Excel doesn't "see" decimals that terminate with 0. 73.00 In order to display the 0s you'd normally have to format as NUMBER 2 decimal places. But 73.00 is only the *displayed* value. The true underlying value is 73. So, the formula will work for numbers like: 73.09 10.02 0.05 But will not work for numbers like: 73.00 10.00 To count only numbers that terminate with 0: =SUMPRODUCT(--(MOD(range,1)=0)) -- Biff Microsoft Excel MVP "Graham" wrote in message ... Many thanks to you both for your help. Toppers, your solution seems a much easier way of creating a dynamic range ? However it is stilll returning a value of '0' for ".0", whereas it is correct for the digits .1 - .9 ? "Toppers" wrote: try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",INDIRECT("$B$39:B"&A2))))) "Graham" wrote: HI I have a Column (B) of 4 figure numbers to 2 decimal places. I want to extract the frequency of each digit (0-9) in the First decimal place only. Thanks to previous help I can get this to work over a fixed range e.g. B39:B74, but as the column is added to on a daily basis, I would like to calculate this over a dynamic range, indexed from the Row value in A2. The following formula works ( for .2) over a static range : =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",$B$39:B74)))) but in trying to adapt it to a dynamic range, I simply get a value returned of "0" ? =SUMPRODUCT(--(ISNUMBER(SEARCH(OFFSET(B39,0,0,$A$2-ROW(B39)+1,1),".2")))) Can anyone see where I'm going wrong ? Many Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HLP - Dynamic Range for Nested IF | Excel Worksheet Functions | |||
| Nested Functions | Excel Discussion (Misc queries) | |||
| Dynamic cell range for functions | Excel Worksheet Functions | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| nested if(and) functions | Excel Discussion (Misc queries) | |||
Linear Mode
