Need help in the formula

[cyliyu]

Need help to get the formula corrected.
Just discovered there was an error in the formula in the spreadsheet.

The formula was used to calculate the days the s/no AND d/c last happen compare with 1st time happen.
e.g.
in row 24 in the spreadsheet, the no of days was 362.
In row 25 when the same s/no keyed in but different d/c, the days in row 24 changed to R instead of 362.
The result should be row 25 = ∞ since first time happen and
row 24 should remain as 362 (31 Aug 18 - 31 Aug 17)

=IF(E23="","",IF(B23="","Error",IF(E23=0,"",IF(E23=1,"∞",IF(COUNTIF(C23:C$30,C23)=1,LARGE(IF($C$6:$C$30=C23,$B$6:$B$30),1)-LARGE(IF($C$6:$C$30=C23,$B$6:$B$30),2),"R")))))

 

[Peter Bartholomew]

Shouldn't you be matching values in column D as well as column C in the LARGE function parameters?

 

[cyliyu]

Yes, Tried, but not working. will try again. thanks.

Updates:- Think I got it works.

=IF(E23="","",IF(B23="","Error",IF(E23=0,"",IF(E23=1,"∞",IF(COUNTIFS(C23:C$30,C23,D23:D$30,D23)=1,LARGE(IF($C$6:$C$30=C23,IF($D$6:$D$30=D23,$B$6:$B$30)),1)-LARGE(IF($C$6:$C$30=C23,IF($D$6:$D$30=D23,$B$6:$B$30)),2),"R")))))

 

[Peter Bartholomew]

With structured references

 

[cyliyu]

Thanks, Peter.
I learnt a new method - Structured References.

 

[Peter Bartholomew]

… there is also the named formula 'previousDate' that saves the effort of using CSE for the array formula!

 

[cyliyu]

… there is also the named formula 'previousDate' that saves the effort of using CSE for the array formula!

Will find out more. thanks.