formula involving buckets

[Taylor]

I'm trying to set up a formula to do the following. I would like cell E90 to
give a text output. The text is dependent upon 2 other cells in the workbook
(Q88 and T87). I want the text in E90 to say "Full Discount Achieved" if Q88
and T87 reach the highest bucket. Otherwise, if either of cells Q88 or T87
do not have a value in the highest bucket, I want the text output to say "X
amount in Q88 or Y Amount in T87 needed to reach next discount level". There
are 3 buckets for Q88 and 6 buckets for T87. I've tried using the IF
formula, but I don't know how to get the output to show how much is needed to
achieve the next bucket, rather than the full discount.

Sheet 1 contains the buckets and another section of the workbook uses the
VLOOKUP formula to determine which discount level is achieved.

Any help is greatly appreciated. Thanks!

 

[Tom Hutchins]

To get this to work, I had to add a zero bucket to the beginning of each
bucket range, so the 3-cell bucket range becomes 4 cells, and the 6-cell
range becomes 7 cells. I put the 4-cell range of buckets for Q88 in AA1:AA4,
and the 7-cell range of buckets for T87 in AC1:AC7. Then I entered this
formula in E90:

=IF(AND(Q88=MAX(AA1:AA4),T87=MAX(AC1:AC7)),"Full discount
achieved",IF(ISERROR(INDEX(AA1:AA4,MATCH(Q88,AA1:AA4)+1,1)),0,INDEX(AA1:AA4,MATCH(Q88,AA1:AA4)+1,1)-Q88)&"
in Q88 or
"&IF(ISERROR(INDEX(AC1:AC7,MATCH(T87,AC1:AC7)+1,1)),0,INDEX(AC1:AC7,MATCH(T87,AC1:AC7)+1,1)-T87)&" in T87 needed to reach next discount level")

You would need to edit the 4-cell and 7-cell bucket ranges to match your
workbook. If you named the 4-cell range Rng1 and the 7-cell range Rng2, you
could use this formula in E90:

=IF(AND(Q88=MAX(Rng1),T87=MAX(Rng2)),"Full discount
achieved",IF(ISERROR(INDEX(Rng1,MATCH(Q88,Rng1)+1,1)),0,INDEX(Rng1,MATCH(Q88,Rng1)+1,1)-Q88)&"
in Q88 or
"&IF(ISERROR(INDEX(Rng2,MATCH(T87,Rng2)+1,1)),0,INDEX(Rng2,MATCH(T87,Rng2)+1,1)-T87)&" in T87 needed to reach next discount level")

Hope this helps,

Hutch

 

[Taylor]

Hutch-

Thanks so much! That's perfect.