Array formula not working properly

[Jack Schitt]

{=SUM(OFFSET(C19,0,ROW(INDIRECT("1:7"))))}
Intended to produce the same answer as
=SUM(D19:J19)
not array entered.
Instead it simulates
=SUM(D19)

Help? (I need the array method as it is part of a larger array formula)

 

[Jack Schitt]

I also tried
{=SUM(INDIRECT(ADDRESS(ROW(D19),COLUMN(D19:J19))))}
But this also seems to evaluate to =SUM(D19)

 

[Dave Peterson]

maybe:
=SUM(OFFSET(C19,0,1,1,7))

{=SUM(OFFSET(C19,0,ROW(INDIRECT("1:7"))))}
Intended to produce the same answer as
=SUM(D19:J19)
not array entered.
Instead it simulates
=SUM(D19)

Help? (I need the array method as it is part of a larger array formula)

 

[Jack Schitt]

Nope, it needs to be an array formula

 

[Frank Kabel]

Hi
why does it need to be an array formula?. what are you trying to
achieve as Dave's formula returns your desired result?

--
Regards
Frank Kabel
Frankfurt, Germany

Nope, it needs to be an array formula

 

[Jack Schitt]

=SUM(D19:J19) also achieves the same result, without the use of offset.
The reason that it needs to be an array formula is because it is just a
small part of a larger array formula, wherein different calculations are
performed upon each element of the array, dependant on its position (ie
column offset) in the array.

--
Return email address is not as DEEP as it appears

Hi
why does it need to be an array formula?. what are you trying to
achieve as Dave's formula returns your desired result?

--
Regards
Frank Kabel
Frankfurt, Germany

Nope, it needs to be an array formula

 

[Jack Schitt]

I am more interested in an explanation of why the two array formulae I
posted FAIL to work than in an alternative formula that happens to produce
the same result for the limited example given.

-Thanks

--
Return email address is not as DEEP as it appears

Hi
why does it need to be an array formula?. what are you trying to
achieve as Dave's formula returns your desired result?

--
Regards
Frank Kabel
Frankfurt, Germany

Nope, it needs to be an array formula

 

[Frank Kabel]

Hi Jack
it would be best if you provide you total formula and describe whyt
you're trying to do. Dave's suggestion would also work as part of an
array formula

--
Regards
Frank Kabel
Frankfurt, Germany

I am more interested in an explanation of why the two array formulae I
posted FAIL to work than in an alternative formula that happens to produce
the same result for the limited example given.

-Thanks

 

[Jack Schitt]

Hi Frank.

The following array formula now works perfectly, so I am not looking for a
solution that "works" any more:

=SUM(1.5*D19:J19-0.5*(IF(D19:J19<8,D19:J19,8))*(COLUMN(D19:J19)<COLUMN(I19)))

However I remain curious as to why my first attempt failed (even though it
lacks elegance):

=SUM(OFFSET(C19,0,ROW(INDIRECT("1:7")))+0.5*(OFFSET(C19,0,ROW(INDIRECT("1:7")))-(8*((ROW(INDIRECT("1:7")))<6)*(OFFSET(C19,0,ROW(INDIRECT("1:7")))>8))))

Leaving aside the possibility of a typo or flawed overall approach, the SUM
operation does not appear to operate on an array, even if I array enter the
formula, and that is the sole problem that I am trying to get my head
around.

I tested that it did not operate on an array, by simplifying the formula to
just:
=SUM(OFFSET(C19,0,ROW(INDIRECT("1:7"))))
and I noted that it only returns the value of D19, not (as I had expected
and wished) the value of SUM(D19:J19) (in this instance).

--
Return email address is not as DEEP as it appears

Hi Jack
it would be best if you provide you total formula and describe whyt
you're trying to do. Dave's suggestion would also work as part of an
array formula

 

[Frank Kabel]

Hi Jack
just as an alternative if I understood your formula and desired result
correctly

=1.5*SUM(D19:J19)-0.5*8*COUNTIF(D19:H19,"<8")
=1.5*SUM(D19:J19)-4*COUNTIF(D19:H19,"<8")

This should give you the same result

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank.

The following array formula now works perfectly, so I am not looking for a
solution that "works" any more:

=SUM(1.5*D19:J19-0.5*(IF(D19:J19<8,D19:J19,8))*(COLUMN(D19:J19)<COLUMN(
I19)))

However I remain curious as to why my first attempt failed (even though it
lacks elegance):

=SUM(OFFSET(C19,0,ROW(INDIRECT("1:7")))+0.5*(OFFSET(C19,0,ROW(INDIRECT(
"1:7")))-(8*((ROW(INDIRECT("1:7")))<6)*(OFFSET(C19,0,ROW(INDIRECT("1:7"
)))>8))))

Leaving aside the possibility of a typo or flawed overall approach, the SUM
operation does not appear to operate on an array, even if I array enter the
formula, and that is the sole problem that I am trying to get my head
around.

I tested that it did not operate on an array, by simplifying the formula to
just:
=SUM(OFFSET(C19,0,ROW(INDIRECT("1:7"))))
and I noted that it only returns the value of D19, not (as I had expected
and wished) the value of SUM(D19:J19) (in this instance).

 

[Jack Schitt]

It does not seem to give the same result.
With:
D19 = 9
E19 = 2
F19 = 8
G19 = 8
H19 = 8
I19 = 8
J19 = 8

=1.5*SUM(D19:J19)-4*COUNTIF(D19:H19,"<8")
returns a value 72.5

{=SUM(1.5*D19:J19-0.5*(IF(D19:J19<8,D19:J19,8))*(COLUMN(D19:J19)<COLUMN(
I19)))}
returns a value 59.5 (correct)





--
Return email address is not as DEEP as it appears

Hi Jack
just as an alternative if I understood your formula and desired result
correctly

=1.5*SUM(D19:J19)-0.5*8*COUNTIF(D19:H19,"<8")
=1.5*SUM(D19:J19)-4*COUNTIF(D19:H19,"<8")

This should give you the same result

 

[Frank Kabel]

Hi
sorry, misread your original formula. The following array formula is
what I came up with:
=1.5*SUM(D19:J19)-0.5*SUM(IF(D19:H19<8,D19:H19,8))


--
Regards
Frank Kabel
Frankfurt, Germany

It does not seem to give the same result.
With:
D19 = 9
E19 = 2
F19 = 8
G19 = 8
H19 = 8
I19 = 8
J19 = 8

=1.5*SUM(D19:J19)-4*COUNTIF(D19:H19,"<8")
returns a value 72.5

{=SUM(1.5*D19:J19-0.5*(IF(D19:J19<8,D19:J19,8))*(COLUMN(D19:J19)<COLUMN
(
I19)))}
returns a value 59.5 (correct)

 

[Jack Schitt]

Thanks, I did not realise that you could vary the dimensions or the array
within the array formula. Had problems with that in the past.

--
Return email address is not as DEEP as it appears

Hi
sorry, misread your original formula. The following array formula is
what I came up with:
=1.5*SUM(D19:J19)-0.5*SUM(IF(D19:H19<8,D19:H19,8))

 

[Aladin Akyurek]

{=SUM(N(OFFSET(C19,0,ROW(INDIRECT("1:7")))))}

=SUMPRODUCT(N(OFFSET(C19,0,ROW(INDIRECT("1:7")))))

 

[Jack Schitt]

Thanks. This dereferencing malarky seems to crop up repeatedly. I shall
try that before posting in future.

--
Return email address is not as DEEP as it appears

{=SUM(N(OFFSET(C19,0,ROW(INDIRECT("1:7")))))}

=SUMPRODUCT(N(OFFSET(C19,0,ROW(INDIRECT("1:7")))))