View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Héctor Miguel Héctor Miguel is offline
external usenet poster
 
Posts: 434
Default How to divide array formula?

hi, again !

ok, if you don't want write-down your array, you will need an(other) array for the every 3 numbers
in a way that this (new) array can be multiplied by your 12 elements array

let's say you put three "ones" (1) in the range [K1:M1]
you can start your sum operation (every 3) with the following CSE (array) formula:

=sum({0,0,1,1,0,1,1,0,1,1,1,1}*n(transpose(offset( $b$1,,row(indirect("1:12"))+9-(row(a1)*3-3)-1,,12))))

and copy/drag down as needed

hth,
hector.

__ previous __
better if you write-down your array (say) to range [B2:M2]
and start with a formula like one of the following:

op1: =sumproduct(subtotal(9,offset($b$2,,row(a1)*3-3,,3)))
op2: =sum(index($b$2:$m$2,row(a1)*3-2):index($b$2:$m$2,row(a1)*3))

copy/drag down as needed

__ OP __
Hello every one,
I got this array:
{0,0,1,1,0,1,1,0,1,1,1,1}
Is there a way to SUM every 3 numbers?
Desired result:
SUM (0,0,1)
SUM (1,0,1)
SUM (1,0,1)
SUM (1,1,1)
It would return:
1
2
2
3
Thank you,