G-Day excel forum, I've attached an analogue of a spreadsheet i need to setup within which i need to find a maximum value (in d4) for all numbers in the 'z' column for which the adjacent x and y values are within the upper and lower x and y boundaries (this makes more sense with the spreadsheet open, hopefully). I've been searching around the net for a couple of hours but nothing seems to work...
Last edited by MattRNR; 02-26-2013 at 11:13 PM.
P.S. the simpler the better...
hi MattRNR, it's great you uploaded a file. what you could do better is to highlight those that match the criteria. otherwise, i'm a little confused. you mentioned lower x & y boundaries, but i see you label 2 y boundaries. so if G2 is for lower x boundary & G3 is for lower y boundary, then maybe an array like this:
=MAX(IF(C7:C16>=G2,IF(D7:D16<=G3,E7:E16)))
this means if C7:C16>=G2 AND D7:D16<=G3, show me the values of E7:E16. i would be given values of
{FALSE;5;FALSE;13;FALSE;2;FALSE;2;FALSE;FALSE}
the MAX of these values is 13. maybe not what you need, but probably along this line
Edit: to make it work, you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it
Last edited by benishiryo; 02-26-2013 at 11:34 PM.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Sorry mate I probably didn’t explain it very well, I’ll attach a new spreadsheet to this post, I’m trying to find the maximum value for which the corresponding values in both the X and Y columns are within that variables (X or Y) set range of values (upper and lower boundary values). So for the X variable the range is 5 – 10, that means that points 1,3,4,8 and 9 are within the range, for the Y variable the range is 10 – 15 so points 3, 4, 7, 9 and 10 are within the range and for both variables points 3, 4 and 9 are within the range. The value I need to calculate with the equation is therefore the maximum z value for points 3, 4 and 9, which in this example should = 13.
The method you suggested isn’t far off what I need but it must consider both variable columns (x and y).
try this array then:
=MAX((C10:C19>=C6)*(C10:C19<=C7)*(D10:D19>=D6)*(D10:D19<=D7)*(E10:E19))
same thing. CTRL + SHIFT + ENTER
Cheers mate, that worked perfectly, i knew it would be something simple like that but just couldn't get it to work, thanks for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks