+ Reply to Thread
Results 1 to 6 of 6

Averaging Array Formula

  1. #1
    Michael Link
    Guest

    Averaging Array Formula

    Hi:

    The inelegant formula below is an attempt to average values in G10, J10,
    M10, and P10 if the cell to the right of each one is greater than 4. (That
    is, if the cell to the right is 4 or less, the number to the left needs to be
    tossed out of the calculation completely and only the remaining numbers
    averaged):

    =IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10)))

    As you can see, though, instead of tossing out the number to the left
    completely, it just uses a zero instead, which completely screws the average
    I need. I've tried substituting double quotes for the zeroes, but of couse
    that doesn't work, either.

    Any ideas on how this whole thing can be reconfigured as an array formula to
    do what I need it to do? I keep getting lost in the syntax and getting
    useless results.

    Help!

    Thanks in advance!

    Michael Link

  2. #2
    Bernie Deitrick
    Guest

    Re: Averaging Array Formula

    Michael,

    =SUM(IF(H10>=4,G10,0),IF(K10>=4,J10,0),IF(N10>=4,M10,0),IF(Q10>=4,P10,0))/SUM(IF(H10>=4,1,0),IF(K10>=4,1,0),IF(N10>=4,1,0),IF(N10>=4,1,0))

    HTH,
    Bernie
    MS Excel MVP


    "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    news:7E55EB07-3DB0-4194-9E4A-80084A2EADF5@microsoft.com...
    > Hi:
    >
    > The inelegant formula below is an attempt to average values in G10, J10,
    > M10, and P10 if the cell to the right of each one is greater than 4. (That
    > is, if the cell to the right is 4 or less, the number to the left needs to be
    > tossed out of the calculation completely and only the remaining numbers
    > averaged):
    >
    > =IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10)))
    >
    > As you can see, though, instead of tossing out the number to the left
    > completely, it just uses a zero instead, which completely screws the average
    > I need. I've tried substituting double quotes for the zeroes, but of couse
    > that doesn't work, either.
    >
    > Any ideas on how this whole thing can be reconfigured as an array formula to
    > do what I need it to do? I keep getting lost in the syntax and getting
    > useless results.
    >
    > Help!
    >
    > Thanks in advance!
    >
    > Michael Link




  3. #3
    Biff
    Guest

    Re: Averaging Array Formula

    Hi!

    Try this:

    Entered as an array using the key combo of CTRL,SHIFT,ENTER:

    =AVERAGE(IF(MOD(COLUMN(G10:Q10),3)=1,IF(MOD(COLUMN(H10:Q10),3)=2,IF(H10:Q10>4,G10:Q10))))

    NB: if all 4 cells to the right are <=4 then you'll get a #DIV/0! error. I
    can put an error trap in the formula which will make it longer.

    Biff

    "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    news:7E55EB07-3DB0-4194-9E4A-80084A2EADF5@microsoft.com...
    > Hi:
    >
    > The inelegant formula below is an attempt to average values in G10, J10,
    > M10, and P10 if the cell to the right of each one is greater than 4. (That
    > is, if the cell to the right is 4 or less, the number to the left needs to
    > be
    > tossed out of the calculation completely and only the remaining numbers
    > averaged):
    >
    > =IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10)))
    >
    > As you can see, though, instead of tossing out the number to the left
    > completely, it just uses a zero instead, which completely screws the
    > average
    > I need. I've tried substituting double quotes for the zeroes, but of couse
    > that doesn't work, either.
    >
    > Any ideas on how this whole thing can be reconfigured as an array formula
    > to
    > do what I need it to do? I keep getting lost in the syntax and getting
    > useless results.
    >
    > Help!
    >
    > Thanks in advance!
    >
    > Michael Link




  4. #4
    Bernie Deitrick
    Guest

    Re: Averaging Array Formula

    Or the array formula - entered using Ctrl-Shift-Enter

    =AVERAGE(IF((MOD(COLUMN(G10:Q10),3)=2)*(G10:Q10)>=4,F10:P10,""))

    HTH,
    Bernie
    MS Excel MVP


    "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
    news:eKmCSIOAGHA.832@tk2msftngp13.phx.gbl...
    > Michael,
    >
    > =SUM(IF(H10>=4,G10,0),IF(K10>=4,J10,0),IF(N10>=4,M10,0),IF(Q10>=4,P10,0))/SUM(IF(H10>=4,1,0),IF(K10>=4,1,0),IF(N10>=4,1,0),IF(N10>=4,1,0))
    >
    > HTH,
    > Bernie
    > MS Excel MVP
    >
    >
    > "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    > news:7E55EB07-3DB0-4194-9E4A-80084A2EADF5@microsoft.com...
    >> Hi:
    >>
    >> The inelegant formula below is an attempt to average values in G10, J10,
    >> M10, and P10 if the cell to the right of each one is greater than 4. (That
    >> is, if the cell to the right is 4 or less, the number to the left needs to be
    >> tossed out of the calculation completely and only the remaining numbers
    >> averaged):
    >>
    >> =IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10)))
    >>
    >> As you can see, though, instead of tossing out the number to the left
    >> completely, it just uses a zero instead, which completely screws the average
    >> I need. I've tried substituting double quotes for the zeroes, but of couse
    >> that doesn't work, either.
    >>
    >> Any ideas on how this whole thing can be reconfigured as an array formula to
    >> do what I need it to do? I keep getting lost in the syntax and getting
    >> useless results.
    >>
    >> Help!
    >>
    >> Thanks in advance!
    >>
    >> Michael Link

    >
    >




  5. #5
    Biff
    Guest

    Re: Averaging Array Formula

    Ooops!

    I have the range references messed up!

    Should be:

    =AVERAGE(IF(MOD(COLUMN(G10:P10),3)=1,IF(MOD(COLUMN(H10:Q10),3)=2,IF(H10:Q10>4,G10:P10))))

    Biff

    "Biff" <biffinpitt@comcast.net> wrote in message
    news:uHL5gJOAGHA.4080@TK2MSFTNGP14.phx.gbl...
    > Hi!
    >
    > Try this:
    >
    > Entered as an array using the key combo of CTRL,SHIFT,ENTER:
    >
    > =AVERAGE(IF(MOD(COLUMN(G10:Q10),3)=1,IF(MOD(COLUMN(H10:Q10),3)=2,IF(H10:Q10>4,G10:Q10))))
    >
    > NB: if all 4 cells to the right are <=4 then you'll get a #DIV/0! error. I
    > can put an error trap in the formula which will make it longer.
    >
    > Biff
    >
    > "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    > news:7E55EB07-3DB0-4194-9E4A-80084A2EADF5@microsoft.com...
    >> Hi:
    >>
    >> The inelegant formula below is an attempt to average values in G10, J10,
    >> M10, and P10 if the cell to the right of each one is greater than 4.
    >> (That
    >> is, if the cell to the right is 4 or less, the number to the left needs
    >> to be
    >> tossed out of the calculation completely and only the remaining numbers
    >> averaged):
    >>
    >> =IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10)))
    >>
    >> As you can see, though, instead of tossing out the number to the left
    >> completely, it just uses a zero instead, which completely screws the
    >> average
    >> I need. I've tried substituting double quotes for the zeroes, but of
    >> couse
    >> that doesn't work, either.
    >>
    >> Any ideas on how this whole thing can be reconfigured as an array formula
    >> to
    >> do what I need it to do? I keep getting lost in the syntax and getting
    >> useless results.
    >>
    >> Help!
    >>
    >> Thanks in advance!
    >>
    >> Michael Link

    >
    >




  6. #6
    B. R.Ramachandran
    Guest

    RE: Averaging Array Formula

    Hi,

    Try the following formula [which is also not very elegant(!)]

    =SUM(IF(H10>4,G10,0)+IF(K10>4,J10,0)+IF(N10>4,M10,0)+IF(Q10>4,P10,0))/SUM(IF(H10>4,1,0)+IF(K10>4,1,0)+IF(N10>4,1,0)+IF(Q10>4,1,0))

    If none of the values in H10, K10, N10, Q10 is greater than 4, the formula
    will return
    #DIV/0! error. If you want to avoid that error showing up, try the
    following modification.

    =IF(SUM(IF(H10>4,1,0)+IF(K10>4,1,0)+IF(N10>4,1,0)+IF(Q10>4,1,0))>0,SUM(IF(H10>4,G10,0)+IF(K10>4,J10,0)+IF(N10>4,M10,0)+IF(Q10>4,P10,0))/SUM(IF(H10>4,1,0)+IF(K10>4,1,0)+IF(N10>4,1,0)+IF(Q10>4,1,0)),"")

    Regards,
    B. R. Ramachandran

    "Michael Link" wrote:

    > Hi:
    >
    > The inelegant formula below is an attempt to average values in G10, J10,
    > M10, and P10 if the cell to the right of each one is greater than 4. (That
    > is, if the cell to the right is 4 or less, the number to the left needs to be
    > tossed out of the calculation completely and only the remaining numbers
    > averaged):
    >
    > =IF(ISERROR(AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10))),"",AVERAGE(IF(H10<=4,0,G10),IF(K10<=4,0,J10),IF(N10<=4,0,M10),IF(Q10<=4,0,P10)))
    >
    > As you can see, though, instead of tossing out the number to the left
    > completely, it just uses a zero instead, which completely screws the average
    > I need. I've tried substituting double quotes for the zeroes, but of couse
    > that doesn't work, either.
    >
    > Any ideas on how this whole thing can be reconfigured as an array formula to
    > do what I need it to do? I keep getting lost in the syntax and getting
    > useless results.
    >
    > Help!
    >
    > Thanks in advance!
    >
    > Michael Link


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1