| 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 |
| 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) | |||
Hybrid Mode
