min, match, offset problem

seanh1016

Active Member
Joined
Feb 16, 2003
Messages
254
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 AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0


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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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