Hello Excel Forum!
I am attempting to output a value based on 4 different min/max range constraints. I have tried using INDEX with a nested IF but have been unable to determine a solution. Please see attached spreadsheet for a more detailed description on the problem.
Thanks,
Andrew
Last edited by myersac; 04-30-2013 at 04:14 PM.
Instead of looking up the values from the models sheet, why not just calculate the slope and index based on the input values on the main sheet ?
Elegant Simplicity............. Not Always
The slope and intercept are calculated from a separate, larger data set - so I am unable to do that.
Hello,
Welcome to the forum. You can use this Array formula
Confirm with Ctrl-Shift-Enter. If the formula is wrapped inside a { } then you did it right.
Please Login or Register to view this content.
I'm sorry it's not elegant, but it does the job.
Here is your sample with formula.
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
If there is only one unique combination in your tables that meet the criteria, then you could use this in E2 for the slope:
=SUMPRODUCT((A2>=models!E2:E401)*(A2<=models!F2:F401)*(B2>=models!G2:G401)*(B2<=models!H2:H401)*(C2>=models!I2:I401)*(C2<=models!J2:J401)*(D2>=models!K2:K401)*(D2<=models!L2:L401),models!M2:M401)
and this in F2 for the intercept:
=SUMPRODUCT((A2>=models!E2:E401)*(A2<=models!F2:F401)*(B2>=models!G2:G401)*(B2<=models!H2:H401)*(C2>=models!I2:I401)*(C2<=models!J2:J401)*(D2>=models!K2:K401)*(D2<=models!L2:L401),models!N2:N401)
Hope this helps.
Pete
Another way would be to have 5 columns on the models sheet the first 4 check if the values entered are within the range. Each one that is returns 1. The fifth column checks if the sum of these is 4 (i.e. all criteria are met) and then returns 1.
Back on the index sheet the slope/intercept values are found looking at the previously mentioned fifth column. Note if there a several matches then the first match is shown.
Say thanks, click *
Hey Lem, thank you for the help. This is very similar to what I was trying only I left out the SMALL and ROW() - 1 parts. Do you think you could share some explanation for how those fit in?
Thanks,
Andrew
Because they are Array, I make the IF formula doing all the condition finding rows by rows, and it will return a list of rows which fit them ( ROW()'s job ). Then, SMALL( that list, 1 ) will find the smallest row among those list (First row fitting all condition)
However, because you have a header row, I have to minus one (it's actually minus the row of where the Reference starts - row 2, and plus 1, but I'm lazy to to type it so I calculated myself and put -1 in there. The full part should have been ROW($A$2:$A$401)-ROW($A$2)+1 ). For example, the Index starts searching from row 2 to row 401 right? So for Index, first result is column 2, second result is column 3, etc ... That's why the -1.
Pete and Harribone,
Thank you so much, it is very helpful to see multiple solutions to the same problem!
-Andrew
Thanks Lem, very helpful explanation.
I've been a forum member for about 2 hours now and am amazed by the speedy and helpful responses. Will definitely do my part to help solve problems in my own comfort zone.
-Andrew
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks