Trouble with OFFSET formula using CELL formula as the "reference" value

[turtle44]

Summary
I'm hoping this long description of my problem is thorough enough for the kind soul that reads it to help me. If you need more information, however, I will provide whatever I can. The crux of the problem is that the OFFSET formula doesn't appear to be letting me use the CELL formula within it to describe a reference argument that varies according to a table I'm using. You can skip to the end to the section "so what am I asking for help with (finally)" if you wish, but I've provided background and other supporting material to set the context.

Background
I am using Excel 2003 to calculate monthly interest income earned over time by a credit card issuer from a series of monthly purchases made by a person that revolves their balance. The values I have at my disposal are:

• A set of fixed monthly interest rates that depend on the type of credit card used
• A set of monthly purchase amounts that vary by month
• A table of monthly balance reduction rates that indicate the percentage of the original purchase that remains in a subsequent month.

In other words:

• In month 1, 100% of a month 1 $100 purchase would be subject to the monthly interest rate and earn interest income for the issuer
• In month 2, 100% of a month 2 $200 purchase would be subject to the monthly interest rate and earn interest income for the issuer, as well as 95% of the month 1 $100 purchase ($95)
• This continues such that in Month N, I must calculate the interest income earned from the remaining balance (reduced by the monthly balance reduction rate) of N different monthly charges

As an example, with these assumptions:

• Monthly interest rate = 1%
• Month 1 purchase = $100
• Month 2 purchase = $90
• Month 3 purchase = $120
• Month 1 remaining balance percentage = 100%
• Month 2 remaining balance percentage = 95%
• Month 3 remaining balance percentage = 85%

I calculate monthly interest income in this way:

• Month 1 interest income = ($100 * 1% * 100%)
• Month 2 interest income = ($90 * 1% * 100%) + ($100 * 1% * 95%)
• Month 3 interest income = ($120 * 1% * 100%) + ($90 * 1% * 95%) + ($100 * 1% * 85%)

My inadequate solution:
The formula below properly calculates the interest earned in Month N for the current month's and previous months' purchases by using the SUMPRODUCT formula with the range of monthly purchases in the first half and the range of values I'm multiplying them by (remaining balance rate * interest rate for that given month).

=SUMPRODUCT($G40:OFFSET($G40,,,,BQ$2),OFFSET($AT$8,,,,-BQ$2):$AT$8)

Because of the way the SUMPRODUCT formula works (at least to my knowledge), I've had to set up the purchase table to go from left to right, starting at the left with month 1, and the balance rate * interest rate table to go from right to left, starting at the right with month 1. I may not be explaining it well, but it works fine. Here's a sample data set.

Data set:
Sorry, I can't figure out how to show this properly...

Month counter
Months 1-12
COL Address BP BQ BR BS BT BU BV BW BX BY BZ CA
Row 2 Values 1 2 3 4 5 6 7 8 9 10 11 12

Combined Interest Rate and Balance Reduction Rate
Months reversed 12-1 to accommodate SUMPRODUCT formula requirements
COL Address AI AJ AK AL AM AN AO AP AQ AR AS AT
Row 8 Values .32 .40 .44 .45 .50 .67 .70 .78 .85 .92 1.0 1.1

Monthly Purchases
Months 1-12
COL Address G H I J K L M N O P Q R
Row 40 Values $100 $120 $200 $300 $150 $800 $300 $450 $700 $125 $100 $350

• $G40 contains the dollar volume of the purchases for month 1
• ($G40:Offset $G40,,,,BQ$2) simply extends the range to the right to include N (value in BQ2) months to be used in the sumproduct formula
• $AT$8 contains the percentage interest income earned on the most recent month's purchases
• OFFSET($AT$8,,,,-BQ$2):$AT$8 simply extends the range to the left to include N (value in BQ2) months of the percentages I need to multiply the monthly purchases by
  

So what am I asking for help with (finally)?
I would like to modify the formula that works
=SUMPRODUCT($G40:OFFSET($G40,,,,BQ$2),OFFSET($AT$8,,,,-BQ$2):$AT$8)

such that the cell address I'm using in the offset formula above (in bold) is determined by a separate formula.

In otherwords, I'd like to replace the bolded addresses above by formulas that will vary. There are actually 8 potential addresses that I need to use in this formula, so the value of $AT$8 could be $AT$7, $AT$8, $AT$9, $AT$10, $AT$11, $AT$12, $AT$13, or $AT$14, depending on the card type that was used to make the purchase.

I'm able to use the formula
=CELL("address",OFFSET(AT6,EC40,,1)) to return a value of $AT$8. (The result of the formula displays "$AT$8" in the cell without the quotes).

However, when I replace $AT$8 in the original formula with the second formula:
=SUMPRODUCT($G40:OFFSET($G40,,,,BQ$2),OFFSET(CELL("address",OFFSET(AT6,EC40,,1)),,,,-BQ$2):CELL("address",OFFSET(AT6,EC40,,1)))

I get a dialogue box telling me there is an error (and it won't let me save the new formula):

The formula you typed contains an error.

• For Information about fixing common formula problems, click Help.
• To get assistance in entering a function, click OK, then click Function on the Insert menu
• If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (').

I don't know how else to tackle this problem, or even if it's possible to have this cell address be described by a formula...please help!

 

[AdamL]

So what am I asking for help with (finally)?
I would like to modify the formula that works
=SUMPRODUCT($G40:OFFSET($G40,,,,BQ$2),OFFSET($AT$8,,,,-BQ$2):$AT$8)

such that the cell address I'm using in the offset formula above (in bold) is determined by a separate formula.

In otherwords, I'd like to replace the bolded addresses above by formulas that will vary. There are actually 8 potential addresses that I need to use in this formula, so the value of $AT$8 could be $AT$7, $AT$8, $AT$9, $AT$10, $AT$11, $AT$12, $AT$13, or $AT$14, depending on the card type that was used to make the purchase.

I'm able to use the formula
=CELL("address",OFFSET(AT6,EC40,,1)) to return a value of $AT$8. (The result of the formula displays "$AT$8" in the cell without the quotes).

However, when I replace $AT$8 in the original formula with the second formula:
=SUMPRODUCT($G40:OFFSET($G40,,,,BQ$2),OFFSET(CELL("address",OFFSET(AT6,EC40,,1)),,,,-BQ$2):CELL("address",OFFSET(AT6,EC40,,1)))

I get a dialogue box telling me there is an error (and it won't let me save the new formula)

Hi turtle44. Wow, long post.

A few observations:

In the context of what you're trying to achieve, I'm not sure that CELL("address",etc) is necessary; just OFFSET(AT6,EC40,,1) on it's own I would have thought is enough.

Also, $G40:OFFSET($G40,,,,BQ$2) is a bit redundant; as I see it, it just reduces to OFFSET($G40,,,,BQ$2). As is the case with the other range in the SUMPRODUCT.

So... does this achieve the desired result?

=SUMPRODUCT(OFFSET($G40,,,,BQ$2),OFFSET(AT6,EC40,,-BQ$2))

(I apologise if I've misread and oversimplified)

Cheers
A

 

[turtle44]

AdamL,

Awesome! Thanks for solving this so simply.

I think I was taking the CELL formula too literally - when I used it by itself, it returned the text "$AT$8", which I thought was exactly what I needed. When I just used the OFFSET formula by itself, it would return the numeric value located in $AT$8. I thought I needed a formula that generated a cell reference not a numeric value, but clearly I was wrong. When I use the OFFSET formula in place of "$AT$8" within my original formula, it actually works!

To respond to your other point about the ranges within the SUMPRODUCT formula - they are not redundant. The first one sets the leftmost cell at $G40 and extends for BQ$2 months to the right; the second one sets the rightmost cell at $AT$8 and extends for BQ$2 months to the left. If I only listed the OFFSET formula, there would be no range for the SUMPRODUCT function to work with - and it would just multiply the two values specified by the OFFSET functions within it...

THANKS AGAIN! I'm so excited this works!

 

[AdamL]

Hi turtle44, I'm glad it worked out for you.

If I only listed the OFFSET formula, there would be no range for the SUMPRODUCT function to work with - and it would just multiply the two values specified by the OFFSET functions within it...

Without flogging a dead horse, I don't think that statement is quite true. The way you are invoking the OFFSET formula, it will indeed return a range, not just a single cell. For example

=SUM(OFFSET(A1,,,,5))

will sum all the cells from A1 to E1. The 4th and 5th argument refers to how many rows and columns, respectively, are in the range. On the other hand,

=SUM(OFFSET(A1,,5))

will only return the value in the cell 5 to the right of A1 (F1), because if those 4th and 5th arguments are left out, it will default to returning a single cell, not a range.

To test, try using "Evaluate Formula" on both of these:

=SUM(OFFSET(A1,,,,5))
=SUM(A1:OFFSET(A1,,,,5))

and you'll see how they both evaluate to the same thing.

Cheers
A

 

[turtle44]

AdamL,

You know, I think you are right. That would make the formula much easier to read for other users too. I will have to wait until I get back to work to try it out, but I do know you can specify the number of rows to include within the offset formula, so it should work perfectly.

Thanks again.