I have a column with years, blanks, and formulaic blanks (if false, than ""). I am running a sumproduct to identify a cash flow tied to a matching year. because some of the cells are not a serial number (or date), I am #Value!. Any ideas for how to resolve this error (I already tried iferror, but it makes the whole "Year()" false)...
Sumproduct with Year



This is really a two part problem  see attached sample. I need to solve the year/value error issue, but I also need to figure out how to use sumproduct to reconcile a calendar year. Cash flows bump up at a midway point in the year, so I need to divide the lower cash flow allocated to the first "half" of the year (not a true half, just the portion of the year that precedes the bump up date), and the latter "half" that includes the bumps, for a full year reconciliation.
Any help would be truly appreciated.

Hello,
Thanks for your sample file ...
However, could you expand a little ... and provide a detailed layout of all the Cash Flows numbers related to the "first half" of the year ...
as well as the Number you are expecting as the final result generated by your Sumproduct formula ...
Cheers

Hello,
Thanks for your sample file ...
However, could you expand a little ... and provide a detailed layout of all the Cash Flows numbers related to the "first half" of the year ...
as well as the Number you are expecting as the final result generated by your Sumproduct formula ...
Cheers
As always Thank you Carim!
Please see the attached updated sample file  the goal is to use sumproduct to identify the correct widget (in a much longer list in the final model) reconcile the year end cash flows by dates (not sum the widgets)
Thank you again in advance!

Hello,
Thanks for your sample file ...
However, could you expand a little ... and provide a detailed layout of all the Cash Flows numbers related to the "first half" of the year ...
as well as the Number you are expecting as the final result generated by your Sumproduct formula ...
Cheers
As always Thank you Carim!
Please see the attached updated sample file  the goal is to use sumproduct to identify the correct widget (in a much longer list in the final model) reconcile the year end cash flows by dates (not sum the widgets)
Thank you again in advance!


Hello Daniel,
Sorry but I am still confused about your goal ...
What are exactly the steps (and the required calculations) to achieve your goal ... :duh:

Hello Daniel,
Sorry but I am still confused about your goal ...
What are exactly the steps (and the required calculations) to achieve your goal ... :duh:
Thanks Carim, as always your assistance is appreciated.
I have updated the same to include a "proforma" in a method I have used in the past that does work, but isn't flexible enough to achieve future goals.
I have also included the steps. Hopefully, this explains the goal.

Thanks for your updated file ... As soon as I have a moment ... I will dive into your spreadsheet ... :wink:

Thanks for your updated file ... As soon as I have a moment ... I will dive into your spreadsheet ... :wink:
I just noticed an error in the steps description it should've said: "Steps 2 ===> Identify corresponding cash flow in column K, by "Year" reference in column F multiplied by its appropriate yearfrac"

Hi again,
Attached is a proposal  Version 2  in order to dynamically generate your Results Table ...
Hope this will help


Hi again,
Attached is a proposal  Version 2  in order to dynamically generate your Results Table ...
Hope this will help
Thanks for your help!
But unfortunately, this is similar to what I already had. The reason, I need it to be more flexible is, say a widget's cost increase is not grown by percentage; but rather per unit (i.e. rather than dollar*(1+X.XX%)^yperiods. We have Quantity*$X.XX=dollar+(Quantity* $x.xx^Yperiods. The Yperiod needs to be able to nonconsistent/systematic, so the first period might be five years, but the second or third might be 3 or 1 or 7, etc). At the end of the day, what I really need is a way to lookup the widget and the year, and apply the yearfrac, since the month is not likely to change, only the year and period...
Sorry if this is confusing...

To be quite honest with you ... this is indeed quite confusing ... :confused:
However, in order to give it a try ... to get closer to your goal ...
would you mind attaching an updated file which would precisely reflect your differentiated costs increases ... :wink:

To be quite honest with you ... this is indeed quite confusing ... :confused:
However, in order to give it a try ... to get closer to your goal ...
would you mind attaching an updated file which would precisely reflect your differentiated costs increases ... :wink:
Carim, I always appreciate your help (and I have learned a lot from you) I just thought I would share the solution, that I got to work. Please see the attached.

Hello Daniel,
Glad you could sort it out ... :smile:
Nice Array Formula ... !!! :congrats:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!