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

Patiëntnumber of days till diagnoseinclusion
M6-21,001
M3-197,000
M3-190,000
M6270
M3-149,000
M6760
M3-93,000
M2751
M6960
M3-16,001
M21530
M61880
M3330
M4-1318,000
M22560
M4-1235,000
M1-1539,000
M1-1539,000
M23340
M4-1124,000
M1-1414,000
M1-1414,000
M4-1064,000
M32930
M24660
M1-1343,000
M34250
M26020
M1-1231,000
M35170
M1-1142,000
M27320
M35730
M36080
M36220
M28400
M1-986,000
M37270
M1-841,000
M38480
M38900
M1-708,000
M39950
M5-1355,000
M211890
M1-567,000
M311040
M213010
M5-1167,000
M1-405,000
M214320
M312790
M313220
M1-324,000
M5-991,000
M215460
M4731
M4950
M1-188,000
M216560
M41590
M1-140,000
M41920
M315520
M5-796,000
M42430
M217830
M316440
M1-9,001
M1430
M5-622,000
M317560
M11600
M45150
M5-440,000
M45980
M13010
M46280
M46710
M13980
M5-250,000
M48080
M16020
M5-70,000
M5-20,001

<tbody>
</tbody>

<tbody>
</tbody>

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 does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
Seems I still run into some problem, I'll be continuing the my questions in the forum where I first posted HERE
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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