Error trapping in a formula with Matching

**markvdh** · 03-02-2011, 06:46 AM

Hi There
I have attached a spreadsheet which contains my problem.If you open and look at the spreadsheet, I want to trap a N/A which results from a Start Date earlier that the month date contained in row 5 from column K onwards.
Anyone got any ideas? Appreciate any help

**JBeaucaire** · 03-02-2011, 07:29 AM

The formula in K5 should be:

=IF(AND(K$4>=$G5,K$4<=DATE(YEAR($G5),MONTH($G5)+$H5-1,DAY($G5))), $D5, "")

...copied across and down.

**markvdh** · 03-02-2011, 09:44 AM

Thank you for that, but your formula does not allow for escalation nor for fewer payments per annum?

**JBeaucaire** · 03-02-2011, 06:12 PM

I don't see any examples of how fewer payments per annum would be treated. The escalation thing, this formula gives the same results as in your original two-line example:

K5: =IF(K$4>=$G5, $D5 * ((DATEDIF($G5,K$4,"y")*$E5)+1), "")

**markvdh** · 03-03-2011, 02:45 AM

If you change the number of payments per annum in H5 to say, 6, it changes the periodic cashflow. Furthermore the escalation date in F5 can be changed independently of G5. Also, a later dated G5 allows for the cash flow to start at a later date. My real problem is that if I make the start date in G5 earlier than the date in K4, then I get #N/A and thats what I want to trap.I have uploaded the worksheet with the changes described here for illustration.

**JBeaucaire** · 03-03-2011, 02:11 PM

I don't want to keep picking at this issue, so let's make sure you example is 100% complete. Do the two lines given show the ONLY two possible variations here?

Make sure the sample file shows manual mockup of the required results, remove your formulas and put in the values/dates as expected for each scenario. Make sure all scenarios are depicted.

BTW, my formula above has been edited to correct the #Value error.

**markvdh** · 03-04-2011, 04:32 AM

This spreadsheet is part of a larger workbook for determining real estate cash flows. There are enumerable variations so to depict all scenarios is impossible. The variables are fonted in blue. For example an expense may only be payable every month - I would then change cell H5 to 12. It could very well be every second month (H5 to 6), every third month (H5 to 4) every fourth month (H5 to 3), twice per annum (H5 to 2) or once per annum (H5 to 1). The expense could also start in a few month's time - I would then change D5 to the appropriate start date (beyond the date in K4). There could then also be a contracted escalation per annum (E5) which would be effected per annum by the month in F5
I have tried to describe the functionality of the formula, which may not be elegant, but it seems to work. The only problem I have is when I have an expense start date (G5) which is earlier than the cash flow start date (K4). The problem is #N/A which I need to trap.

**JBeaucaire** · 03-04-2011, 10:27 AM

Along the lines of:

=IF($G5<K$4, "", the rest of your formula....)

**markvdh** · 03-07-2011, 05:57 AM

Thank you JBeaucaire for your time on this one. I tried your suggestion, and the banner " there is a problem with the formula . . . " came up. I guess that there is something inherently wrong with my formula for it to not accept your suggestion (although it seems to work fine without any adjustment). Probably back to the drawing board for me. Thanks again