Ask Your Question
0

How can I find specific decimals using array formula?

asked 2017-02-25 01:51:49 +0200

vh gravatar image

On column A3:A15 I have a series of numbers with decimals. 10.2, 13.21, 45.26 and so on. I wrote the following formula:

=SMALL(IF(ISNUMBER(SEARCH(B3,A3:A15)),ROW(A3:A15),""),1)

By typing a decimal in B3, such as ..21, it will give me the row number for the cell with 13.21. But if I type ..20 it will find nothing. If I switch it to .0.2 it will find the row number for the cell with 10.2. But if change the formula to this:

=SMALL(IF(ISNUMBER(SEARCH(B3,A3:A15)),ROW(A3:A15),""),2)

and I type .0.2 on B3, it will give me the row number for 13.21.

What can I type on B3 so it can inly detect numbers with .2 but not .21, .22, .23, etc?

edit retag flag offensive close merge delete

Comments

Where is the array formula you are talking of in the subject?
Are the contents of A3:A15 and of B3 actually of type number or of type text?
Are you aware of the facts that
... SEARCH is working on texts and will thus need to convert numbers to text?
... such an automatic conversion will never return a result without a digit in front of the decimal separator?
... that the coversion will not regard the 'Numbers' format set for the source cell?
What shall I type .0.2 on B3 mean?

Lupp gravatar imageLupp ( 2017-02-25 10:46:37 +0200 )edit

Are you aware of the fact that SEARCH will evaluate its first parameter as a regular expression if the respective option is set? And that the point (full stop) has a special meaning in RegEx?
Using SEARCH with numbers you should explicitly convert them with the help of the TEXT function with appropriate format codes.

Lupp gravatar imageLupp ( 2017-02-25 10:52:41 +0200 )edit

I know SEARCH applies to text. What can I use instead of SEARCH for look for an exact number? See my response to karolus. Basically I need an array formula to find an exact value. If in D I have 1200.45, 1200.11 and 1200, what can I use to find ONLY 1200 and not 1200.45 or 1200.11?

vh gravatar imagevh ( 2017-03-03 23:01:10 +0200 )edit

Also: I need to do this without having to reformat the contents of D column as text. That is simply not possible for the data I will be using.

vh gravatar imagevh ( 2017-03-03 23:01:53 +0200 )edit

You should rather tell what you actually want to achieve, instead of what formula didn't do the job.
There is no problem with converting numbers "on the fly". You didn't talk originally of matcehs for numbers, but for textual pieces as far as I understood. What did you mean by typing .0.2 on B3? What about the first point there? ...

Lupp gravatar imageLupp ( 2017-03-03 23:47:51 +0200 )edit

I'm trying to find the row number in a column that matches a specific number (in this case 1200) without having the search result mixed up with 1200.45, 1200.11, etc. And I'm trying to achieve this using an array formula. All I need is something that will find the correct match of 1200. If SEARCH doesn't work, then what?

vh gravatar imagevh ( 2017-03-04 00:56:34 +0200 )edit

By the way: .0.2 on B3 means I was looking for any number that contained the decimal 0.2 using B3 as reference for the search.

vh gravatar imagevh ( 2017-03-04 00:57:27 +0200 )edit

In short, I'm doing a SEARCH for NUMBERS. SEARCH doesn't do the trick because it's intended for text. So I need something similar to SEARCH that can be used for numbers.

vh gravatar imagevh ( 2017-03-04 01:00:35 +0200 )edit

(I still do not understand the first point . .)
".0.2 on B3 means I was looking for any number that contained the decimal 0.2 using B3 as reference for the search."
To me this again means that you are searching for specific sequences of characters (decimal separator, decimal digits). This is seraching for text. SEARCH or FIND may be the appropriate functions depending on additional info.
Searching for exact numbers would use simply a comparison by =.

Lupp gravatar imageLupp ( 2017-03-04 01:29:56 +0200 )edit

The first point is a wildcard. When you add a dot to a number or to text when doing a search, it means you are looking for any content that has that number or text. Example: if you have 34.31 in A1 and ext.31 in A2, if you search for ..31 you will get A1 and A2 as a correct answer. The . is like the asterisk. It's a wildcard.

vh gravatar imagevh ( 2017-03-04 01:59:31 +0200 )edit

4 Answers

Sort by » oldest newest most voted
0

answered 2017-02-25 16:12:14 +0200

karolus gravatar image

B3 → 0.2

=SMALL(IF(ABS(MOD(A$3:A$15,1)-B$3)<0.0000000001,ROW(A$3:A$15),""),ROW(A1))

enter with ctrl+shift+enter and hold ctrl-key meanwhile pulling down.

edit flag offensive delete link more

Comments

Didn't work. What I need is a formula to find the correct number in an array. Let's say that in column D I have 1200.45, 1200.11, and 1200, and I only want to find out the row number of the cell with 1200. I need to do this using an array formula:

=SMALL(IF(ISNUMBER(SEARCH(1200,$D$2:$D$302)),ROW($D$2:$D$302),""),1)

The problem with that formula is it will return as a match ANY number with 1200 (1200.45, 1200.11, etc), not just 1200. I understand SERCH applies to text. What can I use instead?

vh gravatar imagevh ( 2017-03-03 22:59:45 +0200 )edit
0

answered 2017-03-04 02:40:42 +0200

Lupp gravatar image

updated 2017-03-04 13:56:10 +0200

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.

edit flag offensive delete link more

Comments

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.

vh gravatar imagevh ( 2017-03-04 02:50:34 +0200 )edit

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.

Lupp gravatar imageLupp ( 2017-03-04 03:06:02 +0200 )edit

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 gravatar imagevh ( 2017-03-04 03:37:37 +0200 )edit

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 gravatar imagevh ( 2017-03-04 03:40:22 +0200 )edit

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 gravatar imagevh ( 2017-03-04 03:44:51 +0200 )edit

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.

vh gravatar imagevh ( 2017-03-04 19:38:19 +0200 )edit
0

answered 2017-03-04 07:58:41 +0200

pierre-yves samyn gravatar image

updated 2017-03-04 07:59:18 +0200

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

edit flag offensive delete link more

Comments

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

vh gravatar imagevh ( 2017-03-04 19:40:05 +0200 )edit

1 = TRUE

The formula searches for the first occurrence of TRUE...

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-03-05 06:51:45 +0200 )edit
0

answered 2017-03-04 01:56:59 +0200

vh gravatar image

updated 2017-03-04 05:57:18 +0200

SOLVED AT LAST!

{=SMALL(IF(TRUNC($D$2:$D$100)+$B$1=$D$2:$D$100,ROW($D$2:$D$100),""),1)}

In which B1 contains the decimal to find in the array. The result of the formula is the row number of the cell containing any number with the decimal .30, but not .31, .32, .33, etc.

Prior to finding the solution for decimals I found a solution for finding whole numbers:

After much struggle I figured out a formula to find whole numbers.

{=SMALL(IF($D$2:$D$100=$AG$2,ROW($D$2:$D$100),""),1)}

In which D2:D100 is the column to be searched, and AG2 contains the value to look for. In this case the whole number 1200.

By repeating the formula as an array, and changing the 1 at the end for 2, 3, 4, etc, I find the next row number containing the number 1200. This formula will not confuse 1200 with 1200.45, 1200.11 etc.

Now I have to figure out a similar formula to find the row number of cells that contain decimal numbers such as 0.1, 0.2, 0.3, etc. For example, find the row number if a cell contains the number 1200.20 but not the number 1200.21. (UPDATE: I solved it. See edit and formula above.)

So if you are still interested in helping me out with this, I would really appreciate it. (UPDATE: If you have a different solution to the one I found I'd like to see it too.)

edit flag offensive delete link more

Comments

Oops! I forgot to add the curly brackets to the formula. The formula should be:

{=SMALL(IF($D$2:$D$100=$AG$2,ROW($D$2:$D$100),""),1)}

The brackets, of course, are added by hitting CTRL-SHIFT-ENTER when entering the array formula.

Again, this formula will find whole numbers, not numbers with decimals using a wildcard for the decimal. See my original question to understand what I mean by this.

vh gravatar imagevh ( 2017-03-04 02:08:49 +0200 )edit

To find any text occurring as a part of the text resulting from default conversion of numbers, you can always use the same formula with FIND. If relying on the default conversion is not working how you want/expect you have to use the TEXT function explicitly providing a format code.
If you are searching an array for a numeric value, stick to the comparison by algebraic comparators like = or >=.

Lupp gravatar imageLupp ( 2017-03-04 02:23:49 +0200 )edit

Sounds reasonable. I found a solution using a math formula adding the specific decimal to TRUNC. See the solution in my updated response.

vh gravatar imagevh ( 2017-03-04 03:31:56 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-02-25 01:51:49 +0200

Seen: 95 times

Last updated: Mar 04 '17