Excel: Formula containing a range inside of an array formula

Question

We've got data in Excel that looks like this:

  |    A    |      B       |      C       |
  —————————————————————————————————————————
1 | Amount  | % Complete 1 | % Complete 2 |
2 | $ 1,000 |          25% |          50% |
3 | $   600 |          50% |         100% |
4 | $ 2,500 |          75% |         100% |

Each line item is the cost of a task we've agreed to pay and each "% Complete" column is the percentage complete as of a specific date. We sum up the amounts owed as of that date to pay them based on the percentages, meaning that for the first "% Complete" column we can calculate the amount owed by using the array formula {=SUM(A2:A4*B2:B4)}.

However, subsequent columns need to pay only the difference between what's currently payable and what was already paid before.

To complicate things further:

The data will be blank if there was no change. For example, if something's 25% done as of the first check and still 25% done as of the second check, the corresponding cell in the second check's column will be blank.
The percentages can go down, leading to a negative amount to be paid. For example, if something's 50% done at the first check but progress goes backwards, the second check might have that at 25%, which means money is due back to us.

So for "% Complete" columns after the first, I need the last non-blank cell in the same row in a "% Complete" column prior to the current column. Using this explanation, I was able to create a formula that does that:

=$A2*(C2-LOOKUP(2,1/($B2:B2<>""),$B2:B2))

This will calculate how much is payable for a particular cell, so in the example above, it will produce $250: $1,000 * (50% - 25%). It will keep working as you continue along and will handle finding the last non-blank column.

I'd like to use the above formula in an array formula, but can't figure out how to do so.

So that gets us to the question: when a formula already has a range in it (i.e., the $B2:B2 in the formula above), is it possible to use that in an array formula and, if so, how? In a normal formula, you could covert a cell reference (e.g., A1) into a range (e.g., A:A), but is there some kind of syntax to use a range within a range?

Also happy to consider completely different ways to solve this problem, but due to how this spreadsheet is used, we unfortunately cannot use VBA.

Using your formula: empty cells are like 0%... is that correct? (having 25% | [empty] | 30% leads to 250 | -250 | 50) — Dirk Reichel
But to answer a part of your question: No. You can not split a 2D-array the way you would need it :( — Dirk Reichel
@DirkReichel, not quite... empty cells mean no change. So if #1 is 25%, #2 is blank, and #3 if 50%, that'll mean the work was 25% done at #1, still 25% at #2 (but because there was no change no data was entered by the reviewer), and then increased by another 25% to 50% at #3. — Vincent
As said: using your formula, that happens to me... I am aware of the behavior you want... Was just wondering — Dirk Reichel

Raman Krishnan Raman Krishnan · Accepted Answer · 2017-07-14T01:21:38

To answer the last part of your post, I assume you will be updating and calculating the progress and payments periodwise. In which case, all you need to do is compare the current period with the previous. If such is the case, using the IF() function in a form something like this should serve the purpose.

=IF(Compl2>Compl1,Compl2*Rate,(Compl2*Rate-Compl1*Rate))

Excel: Formula containing a range inside of an array formula

1 Answers