Combining values for threshold lookup

  • Thread starter Thread starter James
  • Start date Start date
J

James

I've got an Excel problem that is really stumping me. I need to find
a way for Excel to tell me when a certain value (specifically, the sum
of 2 cells that isn't constant) has been exceeded. For example,
imagine the following table:


Jan 03 Feb 03 Mar 03 Apr 03
APPLES 2 4 4 5 ...
ORANGES 5 7 10 13 ...
BANANAS 3 5 7 9 ...
....
....
....

I'm trying to create a formula or macro that can tell me what month
the # of Apples + # of Oranges is greater than, lets say 10. So in
this case, in Feb 03, the # Apples + # Oranges = 11, which has met the
threshold criteria, so the formula will return to me "Feb 03". My
list of actual data is about 1600 lines long, but I'd need to be able
to specify which items I wanted to compare (i.e. tell Excel to compare
Bananas to Mangos, and find out what month the total will exceed 10)

I don't think that Hlookup of Vlookup alone is powerful enough to do
it. Do I have to do it as a macro? Are there any Excel wizards that
could give me some advice? I would really appreciate it!

Thanks,
Jimmy
 
USING YOUR DIAGRAM THESE ARE THE START OF THE FORMULAS I'D
USE (KNOWING THE LITTLE BIT OF INFORATION THAT YOU'VE
GIVEN.

COL A COL B COL C
APPLES =SUMIF($A$2:$A$4,$A6,B$2:B$4) =SUMIF
($A$2:$A$4,$A6,C$2:C$4)

ORANGES =SUMIF($A$2:$A$4,$A7,B$2:B$4) =SUMIF
($A$2:$A$4,$A7,C$2:C$4)

BANANAS =SUMIF($A$2:$A$4,$A8,B$2:B$4) =SUMIF
($A$2:$A$4,$A8,C$2:C$4)

10 =IF(B6+B7>$A$9,B1,"nope") =IF
(C6+C7>$A$9,C1,"nope")
 
I've got an Excel problem that is really stumping me. I need to find
a way for Excel to tell me when a certain value (specifically, the sum
of 2 cells that isn't constant) has been exceeded. For example,
imagine the following table:

Jan 03 Feb 03 Mar 03 Apr 03
APPLES 2 4 4 5 ...
ORANGES 5 7 10 13 ...
BANANAS 3 5 7 9 ... ...
I'm trying to create a formula or macro that can tell me what month
the # of Apples + # of Oranges is greater than, lets say 10. So in
this case, in Feb 03, the # Apples + # Oranges = 11, which has met the
threshold criteria, so the formula will return to me "Feb 03". My
list of actual data is about 1600 lines long, but I'd need to be able
to specify which items I wanted to compare (i.e. tell Excel to compare
Bananas to Mangos, and find out what month the total will exceed 10)
Click to expand...

If your table were named TBL, and the numbers in each row were always increasing
from left to right, try the array formula

=OFFSET(TBL,0,MATCH(10,OFFSET(TBL,MATCH("APPLES",INDEX(TBL,0,1),0)-1,1,1,
COLUMNS(TBL)-1)+OFFSET(TBL,MATCH("ORANGES",INDEX(TBL,0,1),0)-1,1,1,
COLUMNS(TBL)-1))+1,1,1)

Replace 10, "APPLES" and "ORANGES" as needed.
 
Try:

=INDEX(B1:M1,MIN(IF((OFFSET(A1,MATCH(M8,A1:A5,0)-1,1,,12)
+OFFSET(A1,MATCH(M9,A1:A5,0)-1,1,,12))>M10,COLUMN(B1:M1)))-
1)

Array-entered (press ctrl/shift/enter), where:

M8 = fruit #1
M9 = fruint #2
M10 = value
B1:M1 = months
A2:A5 = list of fruits.

HTH
Jason
Atlanta, GA
 
Back
Top