number closest to 0

Hi there,

I'm a medical student currently working on a paper for which I'm doing data analysis of patients.
I ran into issue for which I could find no answer on the internet, nor anyone I knew who works with Excel could help me. Hopefully someone here can.


1. Which formule is there to select, in each row, the number closest to zero, so that the cell next to it will get a "1". As depicted in the following picture:
pic1.png

2. To add to that, my aim is to have this formula to select the number closest to zero, for each individual patient. Like in the picture below (no real names used)
pic2.png

3. Finally, I only want the number closest to zero to be selected if it is within a margin of -90 to 90. For example, if one patient has 95 as 'number closest to zero' I don't want it be selected.

I'm really hoping there is a formula to do all this, otherwise I will have to go through hunderds of patients to select all these numbers by hand.

thanks in advance

EDIT:
I missed the rule about crossposting, my apologies. I posted my questions here as well: https://www.mrexcel.com/forum/excel-...ml#post5087891

However, I prefer to continue in this post for this and following issues, if that would be alright.

Post a sample spreadsheet not an image (we cant manipulate them and Im not typing in all that data again). Supply expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

Originally Posted by 63falcondude

Try this in C2:

=--(MIN(IF((A$2:A$38=A2)*(ABS(B$2:B$38)<=90),B$2:B$38))=B2) Ctrl Shift Enter

thanks, but I keep receiving an error notification when I fill in this formula. The 'B$2' gets highlighted as the main error.

Here is a non-CSE alternative:

=--(AGGREGATE(15,6,(A$2:A$10=A2)*(ABS(B$2:B$10)<=90)*(B$2:B$10),1)=B2)

I don't know what error notification you are receiving. It worked fine on my end.
If you are using a non-english version of Excel, the function names may be different.

Edit: Upon further testing, the AGGREGATE formula did not work in all cases.

Try this, unless I have misunderstood your goal it should work.

EDIT: In place of zero I used a tack "-". This can easily be replaced with 0, but I figured it was easier to see the positive results without the 0's and only 1's.
EDIT2: The only thing I hadnt tested for is a tie between 2 columns. In other words if 2 of the 3 columns are both the closest to 0, which do you put a 1 in (or do you do a 1 in both)?

thanks! it's one step closer to my goal at least. Per individual patient, all the numbers within the range of -90 to 90 are now selected, my goal is to select only the number that is closest to 0.

Oh wait, ok you do want to do it per patient, but you have multiple records (rows) per patient as well? So for example M6 should have a 1 next to a number, but only 1 time across all the rows for M6?

yes, that's it pretty much

Heres the updated copy, its an array formula now but should do what you want.

EDIT: I forgot to fill the formula in column G down, sorry

@ Sebtaw:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

I caught some errors, ill post an updated file shortly

Ok so I updated the formula, filled it down the columns and added a filter to your data to make checking it easier. I added some additional columns to test the results and check when a patient doesnt return a 1 someplace that the formula is working correct. IE: M8 returns all 0's, this is because they have 1 record with no value within -90 to 90, thus working correct.

Originally Posted by 63falcondude

Were you referring to me Ali? I quoted one sentence from post #10 and put it into post #16...

No, sorry - I've amended my post.

@sebtaw, Ok thats likely my misunderstanding what you want. I am doing the closest to 0 for a patient across all columns (T=1, T=2 and T=3). I am to understand then it should be per patient for each column separately?

ARRAY formula in H2 then drag down.Pl see note below.

Please Login or Register  to view this content.

@sebtaw I have updated the file and attached it. It is now as follows:

Columns C, E and G show a 1 to indicate that row/record has the number closest to 0 between -90 to 90 for that patient ID in that column/set. Each of the 3 columns/sets are now evaluated independently of each other.

Ex: My earlier attempt would give you a 1 for each patient only 1 time across all three columns, now its possible for a patient to have upto 3x 1's, 1 per column (t=1, t=2, t=3).

Your post does not comply with Rule 8 of our Forum RULES.

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

https://www.mrexcel.com/forum/excel-...ml#post5087715

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Originally Posted by sebtaw

On further note, the array formule that you wrote, is it from -90 through 90 or from -90 till 90?

The way I written the formula it should be -90 to 90 inclusive, ie: -90, -89, -88,... 88, 89, 90

#Value means that there is likely something in your data that the formula cannot calculate, like a number as text. More than likely one or more of the numbers are stored as text, for which you cannot get the ABS of text and get #VALUE instead.

Double check you data and ensure all the numbers being evaluated are actually numeric.

@Zer0Cool,

Please do not respond further until the OP has complied with the crosspost.

Originally Posted by sebtaw

I have provided a link to my crosspost in my first post

Thanks for the update.