Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count first decimal place only
Hi, I want to count the occurence of the first decimal place only in a range
of numbers. The "COUNTIF" function in B1 will work for a whole number as shown below, but if i want the number of times .2 occurs, ignoring all other intergers and decimal places. Can I adapt this function for this? =COUNTIF(A1:A8,"4567.21") A B 1 1234.35 1 2 2345.78 3 3456.98 4 4567.21 5 5678.23 6 6543.56 7 6754.82 8 3452.45 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count first decimal place only
countif is not really a good match for this purpose
=sumproduct(--(floor(mod(A1:A8,1),0.1)=.2)) should do what you want "Graham" wrote: Hi, I want to count the occurence of the first decimal place only in a range of numbers. The "COUNTIF" function in B1 will work for a whole number as shown below, but if i want the number of times .2 occurs, ignoring all other intergers and decimal places. Can I adapt this function for this? =COUNTIF(A1:A8,"4567.21") A B 1 1234.35 1 2 2345.78 3 3456.98 4 4567.21 5 5678.23 6 6543.56 7 6754.82 8 3452.45 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count first decimal place only
Excellent, Many Thanks!
"bj" wrote: countif is not really a good match for this purpose =sumproduct(--(floor(mod(A1:A8,1),0.1)=.2)) should do what you want "Graham" wrote: Hi, I want to count the occurence of the first decimal place only in a range of numbers. The "COUNTIF" function in B1 will work for a whole number as shown below, but if i want the number of times .2 occurs, ignoring all other intergers and decimal places. Can I adapt this function for this? =COUNTIF(A1:A8,"4567.21") A B 1 1234.35 1 2 2345.78 3 3456.98 4 4567.21 5 5678.23 6 6543.56 7 6754.82 8 3452.45 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count first decimal place only
Sorry, spoke to soon. Whilst counting, it appears to be adding certain
figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and have been ommited ? Something needs tweeking ? "Graham" wrote: Excellent, Many Thanks! "bj" wrote: countif is not really a good match for this purpose =sumproduct(--(floor(mod(A1:A8,1),0.1)=.2)) should do what you want "Graham" wrote: Hi, I want to count the occurence of the first decimal place only in a range of numbers. The "COUNTIF" function in B1 will work for a whole number as shown below, but if i want the number of times .2 occurs, ignoring all other intergers and decimal places. Can I adapt this function for this? =COUNTIF(A1:A8,"4567.21") A B 1 1234.35 1 2 2345.78 3 3456.98 4 4567.21 5 5678.23 6 6543.56 7 6754.82 8 3452.45 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count first decimal place only
I am not sure what you mean by adding together
More details please. but another question is this text or numeric. what do you get with =mid(A1,6,1) if this is text use =sumproduct(--(mid(A1:A8,6,1)=2)) also try the equations with just A1 and just A4 instead of A1:A8 "Graham" wrote: Sorry, spoke to soon. Whilst counting, it appears to be adding certain figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and have been ommited ? Something needs tweeking ? "Graham" wrote: Excellent, Many Thanks! "bj" wrote: countif is not really a good match for this purpose =sumproduct(--(floor(mod(A1:A8,1),0.1)=.2)) should do what you want "Graham" wrote: Hi, I want to count the occurence of the first decimal place only in a range of numbers. The "COUNTIF" function in B1 will work for a whole number as shown below, but if i want the number of times .2 occurs, ignoring all other intergers and decimal places. Can I adapt this function for this? =COUNTIF(A1:A8,"4567.21") A B 1 1234.35 1 2 2345.78 3 3456.98 4 4567.21 5 5678.23 6 6543.56 7 6754.82 8 3452.45 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count first decimal place only
You might try:
=SUMPRODUCT(--(ISNUMBER(SEARCH(".2",A1:A8)))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Graham" wrote in message ... Sorry, spoke to soon. Whilst counting, it appears to be adding certain figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and have been ommited ? Something needs tweeking ? "Graham" wrote: Excellent, Many Thanks! "bj" wrote: countif is not really a good match for this purpose =sumproduct(--(floor(mod(A1:A8,1),0.1)=.2)) should do what you want "Graham" wrote: Hi, I want to count the occurence of the first decimal place only in a range of numbers. The "COUNTIF" function in B1 will work for a whole number as shown below, but if i want the number of times .2 occurs, ignoring all other intergers and decimal places. Can I adapt this function for this? =COUNTIF(A1:A8,"4567.21") A B 1 1234.35 1 2 2345.78 3 3456.98 4 4567.21 5 5678.23 6 6543.56 7 6754.82 8 3452.45 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count first decimal place only
Thank you both for your further input. Both formulae work fine with some test
data, but RagDyeR's works with the real data, so I will run with that! It is all numeric data. bj's formula is ading together the *.1 & the *.2 and giving a correct total for the two combined under *.1, and leaving a zero result for the *.2 ? Many thanks to you both again. "RagDyeR" wrote: You might try: =SUMPRODUCT(--(ISNUMBER(SEARCH(".2",A1:A8)))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Graham" wrote in message ... Sorry, spoke to soon. Whilst counting, it appears to be adding certain figures together, e.g. 1&2, 3&4, 6&7 & 8&9 ? 0 & 5 should read Zero, and have been ommited ? Something needs tweeking ? "Graham" wrote: Excellent, Many Thanks! "bj" wrote: countif is not really a good match for this purpose =sumproduct(--(floor(mod(A1:A8,1),0.1)=.2)) should do what you want "Graham" wrote: Hi, I want to count the occurence of the first decimal place only in a range of numbers. The "COUNTIF" function in B1 will work for a whole number as shown below, but if i want the number of times .2 occurs, ignoring all other intergers and decimal places. Can I adapt this function for this? =COUNTIF(A1:A8,"4567.21") A B 1 1234.35 1 2 2345.78 3 3456.98 4 4567.21 5 5678.23 6 6543.56 7 6754.82 8 3452.45 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Place Rounding | Excel Worksheet Functions | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
changing to two place decimal | Excel Worksheet Functions | |||
Fixed decimal place | Setting up and Configuration of Excel |