Prev: converting db need six digit w leading zeros,
Next: WHEN I TOTAL A COLUMN I GET A RESULT IN WRONG FORMAT
From: Lars-�ke Aspelin on 21 May 2010 00:58
On Thu, 20 May 2010 20:38:45 +0100, Odin
<Odin.63b8d89(a)excelbanter.com> wrote:

>
>I have the following formula that I would like to shorten it. Is it
>possible?
>
>=12.5%*(($A$1=$C1)+(($A$1=$C6)*2)+(($A$1=$C11)*3)+(($A$1=$C16)*4)+(($A$1=$C21)*5))
>
>To following is just an idea but doesn't work on excel.
>
>=12.5%*(($C1,$C6,$C11,$C16,$C21)=$A$1)*{1,2,3,4,5}
>
>I wanted to use SUMPRODUCT but it only take contiguous array. Thanks
>for any input.
>
>PS: I can not use array C1:C21 because other cells within it may equal
>to $A$1 which will return incorrect result. And the 3rd part, {1,2...}
>would be too long.

Try this formula, it is 19 characters shorter than the one you have:

=12.5%*SUMPRODUCT((MOD(ROW(5:25),5)=0)*ROW(5:25)/5*(C1:C21=A1))

Hope this helps / Lars-�ke

 | 
Pages: 1
Prev: converting db need six digit w leading zeros,
Next: WHEN I TOTAL A COLUMN I GET A RESULT IN WRONG FORMAT