Re: Nested functions inc. dynamic range

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <Graham@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2E914EC4-B3E3-45C9-AE9B-D7386D20F421@xxxxxxxxxxxxxxxx
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" <Graham@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:613DF27A-753A-482A-AEE6-EE4F03471C9E@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: Nested functions inc. dynamic range
    ... Microsoft Excel MVP ... In order to display the 0s you'd normally have to format as NUMBER 2 ... calculate this over a dynamic range, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Nested functions inc. dynamic range
    ... Microsoft Excel MVP ... In order to display the 0s you'd normally have to format as NUMBER 2 ... easier way of creating a dynamic range? ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Nested functions inc. dynamic range
    ... Microsoft Excel MVP ... Valko" wrote: ... In order to display the 0s you'd normally have to format as NUMBER ... calculate this over a dynamic range, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Nested functions inc. dynamic range
    ... Valko" wrote: ... Microsoft Excel MVP ... In order to display the 0s you'd normally have to format as NUMBER 2 ... calculate this over a dynamic range, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Kneisner and Doering KWZ30-2
    ... The controls of the KWZ-30/2 are much more comfortable than the controls of ... colour display with a resolution of 320 x 240 pixels. ... passed though a low pass filter, a switchable high pass filter and an ... mixers are followed by two A/D-converters with a dynamic range of 120 dB. ...
    (rec.radio.shortwave)