Forum Discussion
Returning adjacent columns with multiple checks in excel
I drew an example of a graph pointing to the data points I need.
I need to know 3 things from this data:
1. What is the minimum value of "A" which has an adjacent cell in "B" for which "B">="C1" is true.
As a statement, what is the smallest value in A that corresponds with B meeting or exceeded it's target C1.
2. What is the value of "A" that is adjacent to the maximum value of "B"
As a statement, what is the value of A for which B is at it's maximum.
3. What is the maximum value of "A" which has an adjacent cell in "B" for which "B">="C1" is true.
As a statement, what is the greatest value of A that corresponds with B meeting or exceeding it's target C1.
BONUS QUESTION: is it mandatory that "A" and "B" are adjacent to one another? That would save me from having to make a bunch of extra tables.
Thank you all very much, I appreciate any feedback.
Hi Michael,
On which version of Excel you are? For 2016 and Office365 subscribers such functions as MINIFS are available, for #1 that could be
=MINIFS(A:A,B:B,">="&C1)
For other versions the formula could be like
=MIN(IF($B:$B>=$C$1,A:A))
(array one, Ctrl+Shift+Enter),
or regular (not array)
=AGGREGATE(15,6,($A1:$A19)/($B1:$B19>=$C$1),1)
The rest is similar, depends on your version.
Hi Michael,
On which version of Excel you are? For 2016 and Office365 subscribers such functions as MINIFS are available, for #1 that could be
=MINIFS(A:A,B:B,">="&C1)
For other versions the formula could be like
=MIN(IF($B:$B>=$C$1,A:A))
(array one, Ctrl+Shift+Enter),
or regular (not array)
=AGGREGATE(15,6,($A1:$A19)/($B1:$B19>=$C$1),1)
The rest is similar, depends on your version.
- I am using office365.
Thank you very much, the MINIF and MAXIF will perfectly handle 1 and 3, now I just need to figure out how to have excel tell me the value of A for which B is a maximum, even though they are not adjacent.Michael, that's practically the same:
=MAXIFS(A:A,C:C,MAX(C:C)) {=INDEX(A:A,MATCH(1,--(C:C=MAX(C:C)),0))} =AGGREGATE(15,6,($A:$A)/($C:$C=MAX($C:$C)),1)(second formula is array one).
Sample:
I think i understand the problem, but im not sure.
So you have column A, which would be different manually input things. Then you have B, and you want that to be a generated number so that A + B = C?
For example: c = 30
Manually input 10 in A, Column B creates 20
Manually input 15 in A, Column B creates 15
Manually input 20 in A, Column B creates 10
Ryan,
IMHO not. If do for #1 manually
- you have some numbers in A and B plus C1=30;
- filter B on numbers >=30;
- sort A (without expanding) from A to Z
First number in A will be an answer
