Sep 20 2022 06:36 AM
Hi there,
For my thesis I need to do some data cleaning and I'm looking for a way to not have to do this by hand (tens of columns and hundreds of rows). The data I have needs to match a prespecified set of numbers, namely:
256 |
128 |
64 |
32 |
16 |
8 |
4 |
2 |
1 |
0,5 |
0,25 |
0,13 |
0,06 |
0,03 |
0,02 |
0,01 |
The problem is a lot of the data in my database is values such as 0.022, 0.60, 0.10, etc.
I'm looking for a way to convert these numbers to the nearest one from the list above. Rounding functions will not work since it's not a generic interval. I have tried vlookup and combinations of 'index' and 'match' but they all render #N/B for reasons unknown to me.
Does anyone have an idea on how to tackle this? I would be much obliged!
Sep 20 2022 08:53 AM
@jasper1995 Here's a formula that will return the closes match in the reference list which is larger than the value looking up (column B below).
=INDEX($A$3:$A$18,MATCH(IF(B15>256,256,B15),$A$3:$A$18,-1),1)
Hope this helps.
Dexter
Sep 20 2022 09:07 AM
Hi Dexter your formula does not bring the desired result "nearest" but always the next higher value. My formula does it differently. Let's see which solution is the right one for @jasper1995 .
=XLOOKUP(TRUE,ABS($A$3:$A$18-B3)=MIN(ABS($A$3:$A$18-B3)),$A$3:$A$18)
Sep 20 2022 09:09 AM
@dscheikey Agreed. Good point.
Sep 23 2022 02:02 AM - edited Sep 23 2022 02:19 AM
Thank you for the advice! Unfortunately as the other commenter said this still renders the next value up from, and not the nearest one.
EDIT: it works, but when inserting a value of say 0,061 it wil render 0,13 instead of 0,06.
Sep 23 2022 02:03 AM
Sep 23 2022 03:07 AM - edited Sep 23 2022 03:22 AM
I managed to figure out a different way of doing it
Matching number closest to X | ||||
Reference | Halfway point | Cutoff | Data input | Matched value |
256 | 192 | 191,999999 | 0,013 | 0,016 |
128 | 96 | 95,999999 | 0,003 | 0,004 |
64 | 48 | 47,999999 | 0,05 | 0,063 |
32 | 24 | 23,999999 | 0,025 | 0,031 |
16 | 12 | 11,999999 | 0,025 | 0,031 |
8 | 6 | 5,999999 | 9 | 8 |
4 | 3 | 2,999999 | 5 | 4 |
2 | 1,5 | 1,499999 | 0,013 | 0,016 |
1 | 0,75 | 0,749999 | 0,013 | 0,016 |
0,500 | 0,375 | 0,374999 | 0,249 | 0,25 |
0,250 | 0,19 | 0,189999 | 0,251 | 0,25 |
0,130 | 0,0965 | 0,096499 | 0,0000001 | 0 |
0,063 | 0,047 | 0,046999 | 0,064 | 0,063 |
0,031 | 0,0235 | 0,023499 | 200 | 256 |
0,016 | 0,012 | 0,011999 | 130 | 128 |
0,008 | 0,006 | 0,005999 | 0,00222 | 0,002 |
0,004 | 0,003 | 0,002999 | 0 | |
0,002 | 0,0015 | 0,001499 | 0 | |
0,001 | 0,0005 | 0,000499 | 0 | 0 |
0 | 0 | 0 |
Columns are A through E.
A is the reference column.
B is the halfway point between two values in A.
C is the cutoff point between two values in A minus 0,00001.
D is the data column e.g. input.
E is the output value e.g. value in D matched to reference in A.
Managed to do it with this formula using the 'Cutoffs' as conditions for the 'IF' formula to use. I am curous if anyone can find flaws in this or whether I am overlooking anything. D3 is the cell it concerns as the input to be matched here.
=IF(D3>$C$3;$A$3;IF(D3>$C$4;$A$4;IF(D3>$C$5;$A$5;IF(D3>$C$6;$A$6;IF(D3>$C$7;$A$7;IF(D3>$C$8;$A$8;IF(D3>$C$9;$A$9;IF(D3>$C$10;$A$10;IF(D3>$C$11;$A$11;IF(D3>$C$12;$A$12;IF(D3>$C$13;$A$13;IF(D3>$C$14;$A$14;IF(D3>$C$15;$A$15;IF(D3>$C$16;$A$16;IF(D3>$C$17;$A$17;IF(D3>$C$18;$A$18;IF(D3>$C$19;$A$19;IF(D3>$C$20;$A$20;IF(D3>$C$21;$A$21;0)))))))))))))))))))
Sep 23 2022 06:25 AM
Hello Jasper, yes, your formula also works. But you could also have rewritten my formula to INDEX() and MATCH(). XLOOKUP() is no longer so new. It is available from EXCEL 2016.
=INDEX($A$3:$A$22,MATCH(TRUE,ABS($A$3:$A$22-D3)=MIN(ABS($A$3:$A$22-D3)),0))