Shortening or changing a formula

[MartinW]

Hi Group,

I need to shorten the following formula. In my example it only tests for
1 and 2, I will need it to test for all digits 1 to 9.
The target cells are a mixture of contiguous and non contiguous ranges
I could group the contiguous sections together which would shorten it a
little, however, I think my whole approach needs to be changed.

=IF(OR(C7=1, C9=1, C11=1, C13=1, C15=1, C17=1, C19=1, C21=1,
C23=1, D7=1, D9=1, D11=1, E7=1,E9=1, E11=1, F7=1, G7=1, H7=1,
I7=1, J7=1, K7=1),"",1)&IF(OR(C7=2, C9=2, C11=2, C13=2, C15=2,
C17=2, C19=2, C21=2,C23=2, D7=2, D9=2, D11=2, E7=2,E9=2,
E11=2, F7=2, G7=2, H7=2, I7=2, J7=2, K7=2),"",2)&IF(OR(C7=3,C9=3,
.........etc. etc.

Thanks
Martin

 

[Guest]

Hi Martin,

One possibility to test for the occurrence of a number in a contiguous
range, say a 1 in c7:k23, is:

=SUMPRODUCT(--(c7:K23=1))

which counts the number of occurrences.

This can be adapted to search for any number 1 to 9 in the range:

=SUMPRODUCT(--(c7:K23>=1),--(c7:K23<=9))

and the result converted to 0 or 1 for any occurrences:

=--(SUMPRODUCT(--(c7:K23>=1),--(c7:K23<=9))>0)

Hope this helps

Anthony

 

[Guest]

.... or if it is intended to construct an output with "" and the digits (that
are not found),

=IF(SUMPRODUCT(--(c7:K23=1)),"",1)&IF(SUMPRODUCT(--(c7:K23=2)),"",2)
etc.

for the digits that are found:

=IF(SUMPRODUCT(--(c7:K23=1)),1,"")&IF(SUMPRODUCT(--(c7:K23=2)),2,"")
etc.

Anthony

 

[MartinW]

Thanks for the reply Anthony,

My range is mostly non-contiguous and sumproduct won't
return the result I'm looking for.

 

[Lori]

=SUBSTITUTE(SUMPRODUCT((FREQUENCY((C7:K7,C9:E9,C11:E11,C13,C15,C17,C19,C21,C23),{1,2,3,4,5,6,7,8,9})>0)*10^{9;8;7;6;5;4;3;2;1;0}*{1;2;3;4;5;6;7;8;9;0}),0,"")

(remove any "-" signs if they appear when you paste.)

 

[Lori]

I may have misread the spec, the above formula shows all digits that
appear in the range.
To show numbers not appearing in the range simply:

Change > to = in above formula.

 

[MartinW]

Many thanks Lori,

That works brilliantly!!!

Martin

 

[Lori]

Thanks Martin, Happy to help - I also noticed you can replace
SUMPRODUCT with SUM without needing an array formula.