Hi
sometimes I need to enter a number in the format of "2097&2098&2099" in one cell. In this case, your suggested formula is not considering all three numbers to calculate the highest value.
Can you please suggest another formula which can consider this kind of multiple entries too.
Thanks in advance.
What makes you think that the calculation time has anything to do with the use of defined names? You can always eliminate a named formula by substituting each occurrence by the formula it refers to. That is what the Excel calculation engine does (albeit with a small overhead) but, frankly, the chances of Excel performing the task correctly are far better than it would be involving any user/developer.Problem is that excel taking much time to calculate if we define names.
Please Don't Cross-Posting.Dear Bosco
Thanks a lot for your suggested formula.
In the attachment sheet 2, I have used below array formula in cell K7 to find out the missing number from given the minimum and maximum value in cell Q4 and R4, Note that this formula works without defining name ranges.
{=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4)),SMALL(IFERROR(0+TRIM(MID(SUBSTITUTE(K8:K10895,"&",REPT(" ",30)),{1,30,60,90,120},30)),""),ROW(1:10895)),0)),ROW(INDEX($A:$A,$Q$4):INDEX($A:$A,$R$4))),ROW(A1)),"")}
Problem is that excel taking much time to calculate if we define names. So can you suggest another formula like above which can find the highest numbers without name defining in cell R4?
Thanks in Advance.
Function MaxSpecial(DateRng As Range, Year_ As Integer, NumRng As Range) As Long
Dim M
Dim NumArr() As Variant
Dim DateArr() As Variant
Dim MaxNum As Long
Dim T As Long, TA As Long
NumArr = NumRng.Value
DateArr = DateRng.Value
For T = 1 To UBound(NumArr)
If Year(DateArr(T, 1)) = Year_ Then
M = Split(NumArr(T, 1), "&")
For TA = 0 To UBound(M)
If M(TA) > MaxNum Then MaxNum = CLng(M(TA))
Next TA
End If
Next T
MaxSpecial = MaxNum
End Function