How can I find specific decimals using array formula?

[Lupp]

You ( @vh ) may study the attached example.

(Editing with respect to the comments by @vh
I am still confused about this somehow, but I lack the time to read every comment on every contribution here again.
If the solution you give factually is what you wanted, it was about the fractional part of the numbers in A3:A15. Since the internal handling of numbers is not decimal I wouldn’t talk of decimals in the case and surely not specialize on the point as the decimal separator.
In principle the appropriate expression returning the fractional part as you would see it displayed would be: MOD(ABS($A$3:$A$15);1). However, a comparison for equality with $B$3 would suffer from the effect of numerical extinction. Comparison for equality is always somehow problemtic in machine arithmetc.
Your formula will work better with this respect, but it will not return the result you may expect if A3:A15 can contain negative numbers.
Never trust in a formula just based on a few tests. There may be unexpected effects of signs, scale factors (order of magnitude) , and whatever in machine arithmetic because of its dyadic working and its limited precision.

To make your formula also work with negative numbers it should read:

{=SMALL(IF(IF($A$3:$A$15>=0;1;-1)*(TRUNC(ABS($A$3:$A$15))+$B$3)=$A$3:$A$15;ROW($A$3:$A$15);"");1)}.

To make it return all the matching rows at once yo migh use

{=IFERROR(SMALL(IF(IF($A$3:$A$15>=0;1;-1)*(TRUNC(ABS($A$3:$A$15))+$B$3)=$A$3:$A$15;ROW($A$3:$A$15);"");ROW($A$3:$A$15)-ROW($A$3)+1);"")}
(There are disadvantages of array output, however.)

On the other hand you once again stressed “I needed a solution WITHOUT having to convert anything to text.” I still assume a misunderstanding. A solution based on internal conversion (during formula evaluation) might even simplify a specific task without any effects concerning the usage of the sheet. The formula allowing for negative numbers I gave may not be very clear with respect to the intentions. Where interested in decimal digits whether left or right of the decimal separator, you are actually interested in text. The formula might better not veil this fact.

In addition working with helper columns (that may get hidden) will often allow for a better and much clearer structure in solutions. You should consider it. In this case the preparation of the relevant parts of numbers might be done in helper columns to simplify and make readable the formulae. Lack of a proper way of design-documentation is one of the most relevant disadvantages of spreadsheets. Helper columns can also help to relieve this.

[vh]

I understand your logic, but it would be impossible for me change the numbers in the array to text without causing problems elsewhere in the spreadsheet. In the actual spreadsheet the numbers in column D are constantly being updated and there’s no time to change them to text. That is why I need to figure out a formula to find numbers with specific decimals without having to rely on changing the numbers to text manually.

[Lupp]

Let’s stick to the original range of A3:A15 you gave (I created a copy below).
The content of this range in my example is numbers, not text. These numbers are converted automatically on the fly when needed for the evaluation of the formulae. B3 (and B20 respectively) are the cells that must contain text. Otherwise you would have to accept useless complications.

[vh]

I understand. But I needed a solution WITHOUT having to convert anything to text. And I found it (using the original columns and rows):

{=SMALL(IF(TRUNC($A$3:$A$15)+$B$3=$A$3:$A$15,ROW($A$3:$A$15),""),1)}

That did the trick. If I enter 0.1, 0.2, 0.3, etc in B3, the formula will give the row number of ONLY the cells with the specific decimal. That way if A3 has 100.30, A4 has 100.32 and A5 has 100.35, the formula will only pick A3 for the row number and it will not confuse it with .32 etc.

[vh]

Your comments were of great help, however. In order to find this solution I followed your tip of using an algebraic evaluation to find the decimals instead of using SEARCH, which is intended for text. So I do appreciate your comments. Thanks!

[vh]

Oh, and by the way; for those who don’t know, TRUNC removes the decimals from a number. Thus, the following formula:

=TRUNC(500.34)

Will return 500 as a whole number. Likewise, =TRUNC(B3) , in which B3 is 500.34 will generate the same result.

So if I add the decimal I’m looking for to a TRUNC number, the result should be the number with the decimal I’m looking for. By evaluating the number with math it solves the problem of finding specific decimals without confusing them with .31, etc.

[vh]

Lupp: I read your update. You’re right. I might run into trouble with negative numbers. However, for this project I know for a fact that there will never be negative numbers, as I’m evaluating payments. I only get a VALUE! error if the cel being evaluated is empty, but that’s fine. I can fix that. Still, for those who might need a similar solution for negatives, your formula might be the answer.

[PYS]

Hi

Sorry, I have not read all this thread but why not just:

{=MATCH(1;D2:D100-B1=INT(D2:D100);0)}

Refresh by Ctrl+Shift+F9

Regards

[vh]

The 1 is the first row with the correct answer? Can I change it to 2, 3, 4, etc?

[PYS]

1 = TRUE

The formula searches for the first occurrence of TRUE…