Multiple Numbers in one cell

[Anbuselvam K]

Hi

In the attached sheet, cell A10 to A19 I have a value of 10080 to 10089 and the cell A20 I would like to add 10090&10091 and the cell A21 10092&10093&10094

In cell A9 I have used =MAX(A10:A20). My desired result is 10094.

It means I want to add more than one value in a single cell also it should be calculate while using max formula.

Please do the needful and thanks in advance.

 

[bosco_yip]

Try,

In A9, enter :

=AGGREGATE(14,6,--MID(SUBSTITUTE(A10:A21,"&",REPT(" ",30)),{1,30,60,90,120},30),1)

Regards
Bosco

 

[Anbuselvam K]

Dear Bosco

Thanks a lot for your help.

In the same sheet Cell C9 I have added below formula to find out the missing numbers in the range of C10 to C23. earlier it was working well but after entering the multiple value it is not giving desired value.

Can you please modify the below formulation too..

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A:$A,$E$7):INDEX($A:$A,$F$7)),C10:C23,0)),ROW(INDEX($A:$A,$E$7):INDEX($A:$A,$F$7))),ROW(A1)),"")

 

[Anbuselvam K]

Also in Cell F8 to be used below formulation is not showing the date.

=INDEX(B9:B21,MATCH(F7,C9:C21,0))

 

[bosco_yip]

Also in Cell F8 to be used below formulation is not showing the date.

=INDEX(B9:B21,MATCH(F7,C9:C21,0))

Try,

=INDEX($B$9:$B$21,MATCH(1,INDEX(0+ISNUMBER(FIND(F7,$C$9:$C$21)),0),0))

Regards
Bosco

 

[Anbuselvam K]

below formula in Cell C9 should be 10094. But it is not showing the missing value

=AGGREGATE(14,6,--MID(SUBSTITUTE(A10:A21,"&",REPT(" ",30)),{1,30,60,90,120},30),1)

Please find the attached sheet.

 

[Anbuselvam K]

Please ignore the Cell C9 formulation in my previous post.

In C9 below formula is not showing the missing value number.

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A:$A,$E$7):INDEX($A:$A,$F$7)),C10:C23,0)),ROW(INDEX($A:$A,$E$7):INDEX($A:$A,$F$7))),ROW(A1)),"")

(Ctrl+Shift+Enter)

Please do the needful.

 

[bosco_yip]

Please ignore the Cell C9 formulation in my previous post.

In C9 below formula is not showing the missing value number.

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDEX($A:$A,$E$7):INDEX($A:$A,$F$7)),C10:C23,0)),ROW(INDEX($A:$A,$E$7):INDEX($A:$A,$F$7))),ROW(A1)),"")

(Ctrl+Shift+Enter)

Please do the needful.

1] See attached file for the formula used in C9

Regards
Bosco

 

[Anbuselvam K]

Dear Bosco

Thanks a lot your help.

Is there any other formula can be used in Cell C9, as its making hang while calculating after any entry in the sheet. At present I am using manual calculation to avoid often hanging.