Re: Array formula not working properly

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 08/29/04 

Date: Sun, 29 Aug 2004 10:50:59 +0200

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
"Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> schrieb im
Newsbeitrag news:#tSN11ZjEHA.596@TK2MSFTNGP11.phx.gbl...
> 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
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:%23iSLwgQjEHA.3476@tk2msftngp13.phx.gbl...
> > 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
> >
> > "Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> schrieb im
> > Newsbeitrag news:Ol6iINQjEHA.3972@tk2msftngp13.phx.gbl...
> >> 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
> >> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> >> news:e4XHHGQjEHA.3632@TK2MSFTNGP09.phx.gbl...
> >> > 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
> >> >
> >> > "Jack Schitt" <mind-the-gap@DEEPblueyonder.co.uk> schrieb im
> >> > Newsbeitrag news:OISffqPjEHA.1040@TK2MSFTNGP09.phx.gbl...
> >> >> Nope, it needs to be an array formula
> >> >>
> >> >> --
> >> >> Return email address is not as DEEP as it appears
> >> >> "Dave Peterson" <ec35720@msn.com> wrote in message
> >> >> news:4130721C.B675CE12@msn.com...
> >> >> > maybe:
> >> >> > =SUM(OFFSET(C19,0,1,1,7))
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > Jack Schitt wrote:
> >> >> >>
> >> >> >> {=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)
> >> >> >>
> >> >> >> --
> >> >> >> Return email address is not as DEEP as it appears
> >> >> >
> >> >> > --
> >> >> >
> >> >> > Dave Peterson
> >> >> > ec35720@msn.com
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>
>

Relevant Pages

  • Re: Array formula not working properly
    ... Return email address is not as DEEP as it appears "Frank Kabel" wrote in message ... >> 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. ... >>> Frankfurt, Germany ...
    (microsoft.public.excel.misc)
  • Re: Countif two criteria - Date and vendor
    ... > "Frank Kabel" wrote: ... >> for your formula try the array formula: ... In column A is a vendor Vendor Xvendor, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Erase numeric digits only
    ... following array ... >Frank Kabel ... >Frankfurt, Germany ...
    (microsoft.public.excel.misc)
  • Re: Use of Indirect with Row() as part of argument
    ... also just say Aladins response. ... > I think that since the Rowfunction was in an array ... > array element index --- not surprising that Excel doesn't ... >>> Frank Kabel ...
    (microsoft.public.excel.worksheet.functions)
  • Re: get data when 2 conditins are met?
    ... Robert wrote:> Thanks Frank, I got it working with your formula. ... > Question1: Is it possible to use something for the lookup array that> covers the entire column? ... > "Frank Kabel" wrote: ... criterions (there will only be one row that meets both of the ...
    (microsoft.public.excel.worksheet.functions)