Okay Excel junkies...

[urbaindk]

Help me out!

Say I have 2 columns of data, x and y. I want to find the minimum value of y and its corresponding x location. How do I do that?

The minimum of y is easy. It's corresponding x value seems to be tricky.


Thanks!

[binary visions]

Easy.

=LOOKUP(MIN(Y1:Y100),Y1:Y100,X1:X100)

LOOKUP is given in the form LOOKUP(value to find,cells to search in,cells to return value from). Replace those Y and X values with your particular sections of cells, of course.

:thumb: (damn that missing smilie)

[urbaindk]

Okay, that seems to work for a test case. Let me try it on my real data.

[urbaindk]

My data gives me :bonk: #N/A! :banghead: which is a load of crap since "=MIN(Y:Y)" returns a value.

[binary visions]

Hrm... Try doing a manual call. That is, take one of the values you know exists, and do a lookup in a small section around that value. For instance, if Y50 is "123", do =LOOKUP(123,Y45:Y55,X45:X55)

[urbaindk]

Hey BV,

I'm pretty sure my Excel is retarded. It fails on a simple parabolic function y = x^2+x+1. Try finding the min of y and it's location in x for y=x^2+x+1 between -1<x<1 with a step size of dx = 0.1.

The answer is ymin = 0.75 @ x=-0.5. Excel gives x as #N/A. . What gives?

[urbaindk]

Hrm... Try doing a manual call. That is, take one of the values you know exists, and do a lookup in a small section around that value. For instance, if Y50 is "123", do =LOOKUP(123,Y45:Y55,X45:X55)

That seems to work but it isn't all that useful if you have 1000+ points of data to sift through.... Where's a good :going blind: icon?


Edit: The range has to be pretty small too!

[binary visions]

The reason I suggested you try a small section is because that validates the formula so at least you know that's not the source of error.

If you're getting an error in the formula, and an error in your other function, it sounds like you have some bad data somewhere. Try glancing through your data tables to make sure your data hasn't gone screwy somewhere. For the parabolic function, make sure your data columns are formatted as numerics, not text or anything.

[urbaindk]

The reason I suggested you try a small section is because that validates the formula so at least you know that's not the source of error.

If you're getting an error in the formula, and an error in your other function, it sounds like you have some bad data somewhere. Try glancing through your data tables to make sure your data hasn't gone screwy somewhere. For the parabolic function, make sure your data columns are formatted as numerics, not text or anything.


Nope it's not the data or the formatting. Excel is nuts.

Lookup doesn't work.
Vlookup doesn't work either.

Match does work. "=MATCH(MIN(Y:Y),Y:Y,0)" will return the row number of my minimum value just fine even in my giant data file. That blows the funky data theory out of the water.

[urbaindk]

Okay here is why it fails...

From: http://office.microsoft.com/en-gb/assistance/ha010563201033.aspx

"If you want the function to return exact matches, you must sort the values in your table array in ascending order or the function will fail. "

So if y is a function of x, you'd have to solve for x in terms of y and then sort y for the excel function to work. Not very practical if you ask me.

[urbaindk]

Got it! Works every time.

=INDEX(X:X,MATCH(MIN(Y:Y),Y:Y,0))

Thanks BV for pointing me towards the right direction...

[binary visions]

Sorry I wasn't online much last night or this morning, forgot to check back in on this thread and see where you stood.

Lots of Excel functions require sorting, but that's not so bad considering how easy sorting everything is. Glad you got it taken care of.