Re: Array formula not working properly

From: Jack Schitt (mind-the-gap_at_DEEPblueyonder.co.uk)
Date: 08/29/04 

Date: Sun, 29 Aug 2004 11:28:02 +0100

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
"Frank Kabel" <frank.kabel@freenet.de> wrote in message 
news:%231ISNVajEHA.2812@tk2msftngp13.phx.gbl...
> 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
    ... > The following array formula now works perfectly, ... > "Frank Kabel" wrote in message ... >>> Return email address is not as DEEP as it appears ... >>>> Frankfurt, Germany ...
    (microsoft.public.excel.misc)
  • Re: Erase numeric digits only
    ... following array ... >Frank Kabel ... >Frankfurt, Germany ...
    (microsoft.public.excel.misc)
  • Re: Roll your own std::vector ???
    ... For the sake of your comprehension, forget about pointers ... to always provide all of the overhead of a deep copy just in case that is what ... but those of reference types are simply "pointed to" by the ... | type of array element may be a value type or a reference type ??? ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Array Copy Concept Questions
    ... Here is what can happen when using arrays to hold objects of Junk. ... At this point what we have done is executed a deep copy of the references to the Junk objects. ... The arrays are different objects but their elements reference the same Junk objects. ... Bottomline is when you did your deep copy of the pins array you essentially did a deep copy of the references to the objects. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Why Serialization
    ... As I wrote in my previous reply you cannot deep copy an arraylist object. ... (You can copy an array, but if that contains reference types not more than ... in the state it is pver the line without XML serialization. ...
    (microsoft.public.dotnet.general)