Hi Experts,
I have created excel file, I need formula in yellow highlighted box, type will be autocomplete type which most nearest criteria matched, you will be understand when you see excel file and screenshots, any expert assist me.
Thanks in advance,
Farhangul
Array formula (CSE) in H2:
=(INDEX($A$3:$A$22,MATCH(MINIFS($B$3:$B$22,$B$3:$B$22,">="&F2)&MINIFS($C$3:$C$22,$C$3:$C$22,">="&G2),$B$3:$B$22&$C$3:$C$22,0))
Hi leo,
Thanks for helping, formula is showing error "your formula is missing parenthesis--)"
and I'm using excel 2007
Hi leo, formula is not working, I'm using 2007 excel
Array formula in H2 (CTRL+SHIFT+ENTER)
=INDEX($A$3:$A$22,MATCH(MIN(IF($B$3:$B$22>=F2,$B$3:$B$22))&MIN(IF($C$3:$C$22>=G2,$C$3:$C$22)),$B$3:$B$22&$C$3:$C$22,0))
Last edited by LeoSkywalker; 11-02-2018 at 07:30 PM.
Hi,
formula still is not working properly, closet number type is not coming,like 2, 8 closet number of criteria 2,9 but it is not showing correct type here should be D2s_v3 but it is showing E2s_v3
Try
=INDEX($A$3:$A$22,MATCH(MIN(IF($B$3:$B$22>=F2,$B$3:$B$22-F2,10^35)+IF($C$3:$C$22>=G2,$C$3:$C$22-G2,10^35)),
IF($B$3:$B$22>=F2,$B$3:$B$22-F2,10^35)+IF($C$3:$C$22>=G2,$C$3:$C$22-G2,10^35),0))
It won't work, try the pair (33,129), it should return D32s_v3(32,128), however it is F72s_v2(72,144)
farhangul, how do you decide which is closest in the pair (3,12)? will it go to (2,8); (2,16); (4,8); or (4,16)?
Also, in the pair (30,200), the closest to 30 is 20, but the closest to 200 is 160; which takes precedence? CPU or MEM?
I made the formula in such a way that the specs in the virtual machine surpassed the examples required in both CPU and MEM, as yout first examples portrayed those answers.
Last edited by AliGW; 11-03-2018 at 11:11 AM.
Please try, and press Ctrl+Shift+Enter
I2 more priority for CPU
=INDEX($A$3:$A$22,MATCH(MIN(ABS($B$3:$B$22-F2)*5^5+ABS($C$3:$C$22-G2)),ABS($B$3:$B$22-F2)*5^5+ABS($C$3:$C$22-G2),))
1. Sort CPU -> Smallest to Largest.
2. Sort MEM -> Smallest to Largest.
3. Use formula:
=INDEX($A$3:$A$22,MATCH(1,INDEX(($B$3:$B$22>=F2)*($C$3:$C$22>=G2),0),0))
Pair (33,129) should return D32s_v3(32,128), however it still returns F72s_v2(72,144); and if you change sorting to CPU after MEM, it will return D64s_v3 (64,256).
Leo - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
@Bo_Ry,
Pairs:
32 128
31 127
31 129
33 127
33 129
Should all return D32s_v3 (32,128) as they are the closest.
Results in the formula change according to the sorting, but there are several different results.
@AliGW, Ali, sorry, my bad.
Last edited by LeoSkywalker; 11-03-2018 at 11:31 AM.
Leo - see post #13. Please do not ignore a moderator's requests. Thanks.
My sheet get all D32s_v3
Please see attached
That's correct Bo, it's working, waiting for the OP to say OK.
AliGW
Ali, please see #14, You're welcome, but I didn't ignore you request, I had edited my previous post to reflect that I had seen it. I'm sorry the page didn't refresh quick enough to see it as I was editing first, as new messages doesn't seem to appear automatically if you're reading the page.Leo - see post #13. Please do not ignore a moderator's requests. Thanks.
Last edited by LeoSkywalker; 11-03-2018 at 11:56 AM.
Hi Bo_RY,
I have put your formula but it is only taking type by criteria minimum, I need formula which will be minimum OR maximum closet, can you help on this?
Hi Leosky,
I have put your formula but it is also only taking type by criteria minimum, I need formula which will be minimum OR maximum closet, can you help on this?
Hello farhangul
What are the maximum values for the CPU / MEM?
Is it always the CPU less than the MEM?
I think you first have to set all the conditions with expected results.
This would make it easier to find a formula.
What are the results if the criteria are following below?
Are such conditions possible?
1. CPU 63 / MEM 5
2. CPU 62 / MEM 11
3. CPU 16 / MEM 8
regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks