How can we help?

New post
0

No matching values in AVERAGEIF(LOOKUP(...)) results in no value returned

Avatar
ServusConnect, Inc. Admin
None
Follow

 Hi - I am trying to fill in Zeros in an ARRAY where no values are retuned in AVERAGEIF(LOOKUP(...)).  I tried using REPLACE(AVERAGEIF(LOOKUP(...)),BLANK(),0) but it gets ignored. Any suggestions would be appreciated. Thanks.

Date Votes

3 comments

  • 0
    Avatar
    Janice Janczyn

    Hello,

    The AVERAGEIF function uses 2 parameters: condition and data range wher both parameters must have the same number of items. I suspect in your case you want to use the AVERAGE function, but you need to replace the blanks returned by the LOOKUP with 0s before doing the average.

         AVERAGE( REPLACE( LOOKUP(..), BLANK(), 0 ) )

    Thank you,
                  Janice

     

     

  • 0
    Avatar
    ServusConnect, Inc. Admin

    Thanks for your reply.  Actually I did have the 2nd variable and verified that count matched up.

    AVERAGEIF(LOOKUP(...),(...))

    Would you still inject the REPLACE() in the lookup or is there a better solution when using AVERAGEIF?

    Thanks, Jeff

  • 0
    Avatar
    Janice Janczyn

    Hi Jeff,

    In that case, your formula would look something like

         AVERAGEIF( condition, REPLACE( LOOKUP(...), BLANK(), 0 ) )

    where condition is a list of true/false with the same number of items as the LOOKUP.

    My understanding is you would like an average of all the values returned by the LOOKUP, based on some condition (either a separate formula or a reference to another datasource). If there is no condition needed to base your average on, you can just use AVERAGE( REPLACE( LOOKUP(...), BLANK(), 0 ) )

    Thanks,
            Janice

     

Please sign in to leave a comment.