Can you please help with inconsistent formula error?

[Leigh Digons]

In Tab 2016 Actual, in columns BU, CK and so on, I am getting an inconsistent formula error. I did not get an inconsistent formula error before adding the following to the formula:

+IF($AR5="",0,(IF(OR($K5>BM$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AS5)-MAX(BY$2,$AR5))))))))+IF($AT5="",0,(IF(OR($K5>BM$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AU5)-MAX(BY$2,$AT5))))))))+IF($AV5="",0,(IF(OR($K5>BM$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AW5)-MAX(BY$2,$AV5))))))))

January and December should have slightly different formulas than Feb-November, because they are looking up prior year and next year dates which are in different columns than Feb-November dates. But before I add the above to the formula, it worked just fine, no inconsistent formula error.

 

[Leigh Digons]

The full formula for BU column is =IF($AL5="",0,(IF(OR($K5>BY$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AM5)-MAX(BY$2,$AL5))))))))+IF($AN5="",0,(IF(OR($K5>BY$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AO5)-MAX(BY$2,$AN5))))))))+IF($AP5="",0,(IF(OR($K5>BM$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AQ5)-MAX(BY$2,$AP5))))))))+IF($AR5="",0,(IF(OR($K5>BM$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AS5)-MAX(BY$2,$AR5))))))))+IF($AT5="",0,(IF(OR($K5>BM$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AU5)-MAX(BY$2,$AT5))))))))+IF($AV5="",0,(IF(OR($K5>BM$2,$K5=""),(((MAX(0,MIN(EOMONTH(BY$2,0)+1,$AW5)-MAX(BY$2,$AV5))))))))

 

[NARAYANK991]

Hi ,

Before this problem can be looked into , can you explain the following :

1. How will the dates be available in the columns AL through AW ?

Is it that dates will be present from AL onwards , or can dates be present anywhere in the intermediate columns ?

What I mean is , can it happen that AL5 is blank and AN5 has a date in it ? Or is it that a date can be present in AN5 only if there is also a date in AL5 ?

What is supposed to happened if all the cells from AL5 through AW5 are blank ?

Can we not exclude this possibility by using an OR function ? Can the outermost IF not have :

=IF(OR(AL5 = "",AN5 = "",AP5 = "",AR5 = "",AT5 = "",AV5 = "") , 0 ,

I am assuming that dates cannot be present in columns AM , AO , AQ , AS , AU or AW unless there are dates present in columns AL , AN , AP , AR , AT or AV. Is this correct ?

2. What is this formula supposed to do ?

Is each of the conditions being checked independent , so that each IF function is independent of the others , or is the whole formula a set of nested IF functions , one within the other ?

For ease of understanding , I am posting the formula in BU12 in the following format :

=IF($AL12="",0,
  IF(OR($K12>BY$2,$K12=""),MAX(0,MIN(EOMONTH(BY$2,0)+1,$AM12)-MAX(BY$2,$AL12)))) +
  IF($AN12="",0,
  IF(OR($K12>BY$2,$K12=""),MAX(0,MIN(EOMONTH(BY$2,0)+1,$AO12)-MAX(BY$2,$AN12)))) +
  IF($AP12="",0,
  IF(OR($K12>BM$2,$K12=""),MAX(0,MIN(EOMONTH(BY$2,0)+1,$AQ12)-MAX(BY$2,$AP12)))) +
  IF($AR12="",0,
  IF(OR($K12>BM$2,$K12=""),MAX(0,MIN(EOMONTH(BY$2,0)+1,$AS12)-MAX(BY$2,$AR12)))) +
  IF($AT12="",0,
  IF(OR($K12>BM$2,$K12=""),MAX(0,MIN(EOMONTH(BY$2,0)+1,$AU12)-MAX(BY$2,$AT12)))) +
  IF($AV12="",0,
  IF(OR($K12>BM$2,$K12=""),MAX(0,MIN(EOMONTH(BY$2,0)+1,$AW12)-MAX(BY$2,$AV12))))

Narayan