Forum Discussion
challenging date calculation
Twifoo, I solved for at least part of the problem. Though now I've created another one. In attached (revised) version, what I've accomplished is I have set up two individual areas of calculation. The lines from one should equal the sum of the three. The 12 individual calcs at upper right have the advantage of iferror to set themselves to 0. This happens in the cases where the EDATE (which was the find I made that solves for how to backwards calculate my months easily) takes the feeder date back so far, that it predates my model options.
In the sumproduct array version of those calculations (which is what the 2.1 is incorrectly calculating), if you evaluate the formula you'll see that the way I've set it up, the IFERROR doesn't individually try to find the pivot items, and only in those cases give 0...instead it says "if any of these dates are not in the table, ignore the whole batch of results", as opposed to what I want it to say, which is "if any of these dates are not in the table, just ignore that date".
Is there a different way to protect for errors in this part of the formula. Again, I found the evaluate formula tool very helpful for isolating the problem. Just don't know how to restate the functions to fix it.
The second problem (which gets to another question I've posted) is that the reference range of assumptions right now shows 4 dates. Sometimes, I may have only 3 date ranges (sometimes 5, 6, etc.). Just with this example, if you delete the part of the assumptions that are on the right (column E section), you'll see the results. This is where I need to be able to tell the lookup formula "start with the B column for your ranges, but only go to the right the number of spaces that are filled contiguously". This is secondary, and I can probably live without it, but if I could have this as an embedded part of the formula, would be good.
Thanks for the continued effort. At least it's closer
- TwifooMar 30, 2019Silver ContributorWhat I want you to specify is the result you want. You have to manually enter that result based on your given data. Thereby, I will suggest a formula you can use so that you won’t have to manually enter the result you want.
- txrussianguyMar 31, 2019Brass Contributor
Twifoo, to your point, to be specific, I need cell H15 to give me the same result as cell H16, which it's not doing for the several problems I listed in the message I sent along with the last file.
Thanks for your help, if you know how to decipher this.
- txrussianguyMar 30, 2019Brass Contributor
Twifoo, believe the last version I sent, with note, should clarify. I can do the calculations in steps now. Just need to get it more condensed. Hopefully last message and file make sense. If not, let's focus on where that doesn't make sense, and I'll clarify anything still unclear.