Number closest to zero within range

[sebtaw]

Hi,

Im a medical student and for current research I've been trapped for some months due to my lack of knowledge in excel. So far, google or friends couldn't help me out on my specific issues. If there exists a formula out there, it would greatly reduce the amount of hours I spent on this research and spent them on more important parts of the research. Thanks in advance!

1. I'm looking for a formula that select the a number closest to zero, so that the cell next to this number will get a "1" instead of a "0"

2. I only want this number to be selected if the number falls within the range of -90 through 90. For example, if the number closest to 0 is 95, it should not be selected.

3. Here is the difficult part. My aim is to have this formula to select the number closest to zero, for each individual patient. In the file I uploaded, I have included 6 individual patients, numbered M1 through M6.


It seems I cant upload an excel file, so I'll try it this way to show the example

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD]Patiënt[/TD]
[TD]number of days till diagnose[/TD]
[TD]inclusion[/TD]
[/TR]
[TR]
[TD]M6[/TD]
[TD="align: right"]-21,00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]-197,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]-190,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M6[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]-149,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M6[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]-93,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]M6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]-16,00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M6[/TD]
[TD="align: right"]188[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]-1318,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]-1235,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-1539,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-1539,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]334[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]-1124,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-1414,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-1414,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]-1064,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]293[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]466[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-1343,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]602[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-1231,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]517[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-1142,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]732[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]573[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]608[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]622[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]840[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-986,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]727[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-841,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]848[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]890[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-708,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]995[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-1355,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]1189[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-567,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]1104[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]1301[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-1167,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-405,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]1432[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]1279[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]1322[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-324,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-991,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]1546[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-188,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]1656[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]159[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-140,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]1552[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-796,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]243[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M2[/TD]
[TD="align: right"]1783[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]1644[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]-9,00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-622,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M3[/TD]
[TD="align: right"]1756[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]515[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-440,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]598[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]628[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]671[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-250,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M4[/TD]
[TD="align: right"]808[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M1[/TD]
[TD="align: right"]602[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-70,00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]M5[/TD]
[TD="align: right"]-20,00[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So basically I want a formula that, for each individual patient (M-number), I want the number closest to zero to be selected, as long as this number falls withing the range of -90 to 90.

 

[Tetra201]

See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=--AND(MIN(IF($A$2:$A$86=A2,ABS($B$2:$B$86)))<=90,MIN(IF($A$2:$A$86=A2,ABS($B$2:$B$86)))=ABS(B2))

 

[sebtaw]

See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=--AND(MIN(IF($A$2:$A$86=A2,ABS($B$2:$B$86)))<=90,MIN(IF($A$2:$A$86=A2,ABS($B$2:$B$86)))=ABS(B2))

I'm not familiar with arra formula's buth I filled it in and pressed Ctrl+shift+enter, yet an error pops up or nothing happens at all. Is it possible for you to add it in the excel file itsel? Thx in advance!

 

[sebtaw]

Seems, that the formula in my Dutch version of Excel 2016 doesn't accept coma's, so I replaced them with ";". Now it looks like this:

=--AND(MIN(IF($A$2:$A$86=A2;ABS($B$2:$B$86)))<=90;MIN(IF($A$2:$A$86=A2;ABS($B$2:$B$86)))=ABS(B2))

Yet it still gives an error, even when using ctrl+shift+enter, the cell will say: #NAME ?

How do I fix this?

 

[sebtaw]

Never mind! I forgot I had to translate the formulas to Dutch as well, now it works perfectly. Thanks a billion times!!!

 

[jtakw]

Cross posted here: https://www.excelforum.com/excel-formulas-and-functions/1234198-number-closest-to-0-a.html

 

[Fluff]

@sebtaw
Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sebtaw]

Seems I still run into some problem, I'll be continuing the my questions in the forum where I first posted HERE

 

[sebtaw]

therefore, could this thread be closed down please?