Number closest to zero within range

sebtaw

New Member
Joined
Jun 14, 2018
Messages
7
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
bVaUNy

[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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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))
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
Never mind! I forgot I had to translate the formulas to Dutch as well, now it works perfectly. Thanks a billion times!!!
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top