Reducing the steps to calculate deal buildup/waterfall

[Delrio44]

HI, I'm trying to simplify the attached process since I have to repeat for 7 different deal-types and 10 different key outputs per deal... Is there a way to reduce the number of rows it takes to arrive at the total cash inflow number?

THANK YOU!!!!

[CAntosh]

Welcome to the forum!

If I'm understanding your goal correctly, you can simplify things by reversing the years in the "Cash Inflow Per Deal" table. I did so in D23:G23 of the attachment. Once that's done, I used the following formula in D4:

=SUMPRODUCT(A$3:D$3,$D$23:$G$23)

Fill right through N4, and it should return your "Total Cash Inflow" results without having to use the waterfall table at all. Take a look at the attachment to see if it'll work for you:

[Delrio44]

Wow! That works..

do you know if there is a way to reverse the order within a function?

[CAntosh]

Yes, but it's uglier. Try the following in D4, filled right:

=SUMPRODUCT(A$3:D$3,N(OFFSET($D$20:$G$20,0,COLUMNS($D$20:$G$20)-COLUMN(INDIRECT("R1C1:R1C"&COLUMNS($D$20:$G$20),FALSE)))))

That should work on the original table. See attached...