Help with this Formula

I have created this formula but it is giving the result "0", when if fact what should showing is "2", even in the formula checker I gone through it systematically and it only results in "0", this is based on the $c87 cell content being a No.4 and the AV80 cell content being a No.1. I know it is to do with a true/false statement but because of the nesting the formula reading right but results wrong and I can't seem to correct it - can anyone see what I done wrong and point out the error I have made! to me thank-you

the reason for the nesting in this manner is because I need to be able to increment a column of data missing out certain days count to a max driven by a fixed limit relative to that column but then in of column of Identical formula with different fixed Maximum and different start dates but which use the same core data of things like "Days Suspended", "Weekends", "Bank Holidays" etc. these have to work across different years and with differing "Bank Holidays" falling on Different days of the week , also there will but numerous clusters of these column sets for about a maximum of 400 participants, which means that there will be a possibility that the may be as many as 4000 to 5000 columns use this calculation over a period of time. Everything else I done in the workbook for this project is working it is just this one thing - I did have a different method which worked but was to slow as i had to Iterate the sheet because of a circular reference that occurred.

=IF(AND(AT$70<>"",AT87=AT$70),1,IF(AV86="","",IF(OR($C87<>5,$C87<>4,$C87<>3,$C87<>2,$C87<>1,$C87<>0),IF(OR(AND($C87=5,AT$70<>"",AT87=AT$70),AND($C87=4,AT$70<>"",AT87>0,$D87=""),AND($C87=3,AT$70<>"",AT87>0),AND($C87=2,AT$70<>"",AT87>0),AND($C87=1,AT$70<>"",AT87>0),AND($C87=0,AT$70<>"",AT87>0)),IF(AV86=1,AV86+1,IF(AND($C87=0,AT87=""),0,AV86+1)),IF(AND(AV86=1,$D87=""),AV86+1,IF(AND(AV86=1,$D87="Day Suspended"),0,IF(AND($C87=0,AT87=""),0,IF(AND($C87=5,$D87=""),AV78+1,IF(AND($C87=5,$D87="Day Suspended"),AV78,IF(AND($C87=4,$D87=""),AV80+1,IF(AND($C87=4,$D87="Day Suspended"),AV80,IF(AND($C87=3,$D87=""),AV82+1,IF(AND($C87=3,$D87="Day Suspended"),AV82,IF(AND($C87=2,$D87=""),AV84+1,IF(AND($C87=2,$D87="Day Suspended"),AV84,IF(AND($C87=1,$D87=""),AV86+1,IF(AND($C87=1,$D87="Day Suspended"),AV86,AV86+1)))))))))))))),"")))

RE: Help with formula

Hi,

Firstly, your formula is very complex and for that reason I decided to split it for more clarity (from inside to outside):

[1] IF(AND($C87 = 1, $D87 = "Day Suspended"), AV86, AV86 + 1)

[2] IF(AND($C87 = 1, $D87 = ""), AV86 + 1, [1])

[3] IF(AND($C87 = 2, $D87 = "Day Suspended"), AV84, [2])

[4] IF(AND($C87 = 2, $D87 = ""), AV84 + 1, [3])

[5] IF(AND($C87 = 3, $D87 = "Day Suspended"), AV82, [4])

[6] IF(AND($C87 = 3, $D87 = ""), AV82 + 1, [5])

[7] IF(AND($C87 = 4, $D87 = "Day Suspended"), AV80, [6])

[8] IF(AND($C87 = 4, $D87 = ""), AV80 + 1, [7])

[9] IF(AND($C87 = 5, $D87 = "Day Suspended"), AV78, [8])

[10] IF(AND($C87 = 5, $D87 = ""), AV78 + 1, [9])

[11] IF(AND($C87 = 0, AT87 = ""), 0, [10])

[12] IF(AND(AV86 = 1, $D87 = "Day Suspended"), 0, [11])

[13] IF(AND(AV86 = 1, $D87 = ""), AV86 + 1, [12])

[14] IF(AND($C87 = 0, AT87 = ""), 0, AV86 + 1)

[15] IF(AV86 = 1, AV86 + 1, [14])

[16] AND($C87 = 0, AT$70 <> "", AT87 > 0)

[17] AND($C87 = 1, AT$70 <> "", AT87 > 0)

[18] AND($C87 = 2, AT$70 <> "", AT87 > 0)

[19] AND($C87 = 3, AT$70 <> "", AT87 > 0)

[20] AND($C87 = 4, AT$70 <> "", AT87 > 0, $D87 = "")

[21] AND($C87 = 5, AT$70 <> "", AT87 = AT$70)

[22] OR([21], [20], [19], [18], [17], [16])

[23] IF([22], [15], [13])

[24] OR($C87 <> 5, $C87 <> 4, $C87 <> 3, $C87 <> 2, $C87 <> 1, $C87 <> 0)

[25] IF([24], [23], "")

[26] IF(AV86 = "", "", [25])

[27] AND(AT$70 <> "", AT87 = AT$70)

[28] IF([27], 1, [26])

 

Now, I think your formula can be simplified and become more shorter and clear if you explain in more details what actually you try to achieve.

However, note the following inconsistencies in your conditions:

 1) What exactly you try to check with condition [24]? In its current form will always return TRUE, no matter of the value of C87.

 2) Let see condition [23]. First suppose that [22] will return TRUE and we go to [15].

 The problem with [15] is the following: based on that condition [22] return TRUE,

we can assume that AT87 cannot be equal to "" and for that reason [14] will always return AV86 + 1, which make [15] to become in this form:

 IF(AV86 = 1, AV86 + 1, AV86 + 1)

which is meaningless...

If [22] return FALSE we go to [13]. There also can be some improvements, but this depends on your logic of what actually you try to achieve...

Please, share more details of your aim.

 

Best regards.

 

Post edit:

You say $C87 = 4 and AV80 = 1. But what are the values in AT$70, AT87, AV86 and $D87?

I already explain what is the problem with [15] but now let see the other node of [23] - the condition [13].

To get result of 2, AV86 must be = 1 and $D87 must be = "". If they are not, we go to [12].

Here is the place where you can get 0 if AV86 = 1 and $D87 = "Day Suspended" and probably this is your

case and formula stop here and return its result. I assume this, based on that $C87 = 4 and for that reason

there is no way to proceed to [11] and further, and get result of 0.

The only way to get result of 2 is in [8] but only if $D87 = "" and AV86 <> 1.

Nick's picture

you'd need to post an example

you'd need to post an example file...
login, edit your post, and add a file