min, match, offset problem

seanh1016

Active Member
Joined
Feb 16, 2003
Messages
253
I have two columns, column A=months, column B=values. Column B has some negative values in it. How do I find the closest value to zero in column B, then figure out which month this value occurs?

I don't want the lowest number -- just the one closest to zero. Would the min function still work here?

Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

seanh1016

Active Member
Joined
Feb 16, 2003
Messages
253
I'm dealing with payments here. Very rarely will there be a zero in column B. But I guess we'd have to factor that in.
 

seanh1016

Active Member
Joined
Feb 16, 2003
Messages
253

ADVERTISEMENT

meaning maybe if the min function returns a negative number since it's looking the lowest number, we may have to use the absolute value and min together in a formula.

I tried this formula, but I came up with #VALUE!:

=INDEX($B$20,MATCH(MIN($B$20:$B$379),$B$20:$B$379,FALSE),-1)

what am I doing wrong here?

By the way, I would use colo's utility, but I have some sensitive material in this file. Sorry.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
seanh1016 said:
meaning maybe if the min function returns a negative number since it's looking the lowest number, we may have to use the absolute value and min together in a formula.

What I'm asking you is the following: B-range houses one or more zeroes along with neg and pos numbers. The value closest to 0 will invariably be 0. I'd think that 0 as the closest to 0 should be accepted as is.


...
By the way, I would use colo's utility, but I have some sensitive material in this file...

Create a copy of the relevant range. Anonimize the figures and names in the copy. Select a small portion the anonimized copy and post.
 

seanh1016

Active Member
Joined
Feb 16, 2003
Messages
253

ADVERTISEMENT



We're only looking at the 1st and 2nd columns.

I tried =OFFSET(B20,MATCH(MIN($B$20:$F$379),$B$20:$B$379,-1)-1,-1,1,1) and it came up with the 360th month - which is the wrong month in this case. The correct month here would be the 13th month.

Would the SMALL function or ABS have any purpose here?
 

seanh1016

Active Member
Joined
Feb 16, 2003
Messages
253
Yes, not the 14th month b/c it's showing that the loan has been paid off in the 13th month. The payoff month is all I want to show.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
seanh1016 said:
Yes, not the 14th month b/c it's showing that the loan has been paid off in the 13th month. The payoff month is all I want to show.

Now we are getting closer to the right specs: You want in fact the smallest positive value, closest to 0, right?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,095
Members
425,258
Latest member
brentmitchell

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
Top