Average of lease $ amounts in a certain region

11/28/18 Edited 12/09/19

I would like to see the average lease amount per region.  I am cross sheet referencing. The formula for the average works.  But when I add in the second part (countifs) to make sure a PE GL Checkbox has been checked and to specify a region (I do a cell reference) it doesn't seem to work.  For instance, in 1 region I do not have any leases yet its giving me a dollar amount. This is the formula that I have:

 

=AVG({Lease Tracker 2018 Lease Rate Offer in $}, COUNTIFS({Lease Tracker 2018 PE GL Checkbox}, 1, {Lease Tracker 2018 Region}, =$Region1))

 

Any suggestions?

Comments

  • KO,

    As written, your formula would average the entire {Lease Tracker 2018 Lease Rate Offer in $} and then include the COUNTIF in that average. COUNTIF will calculate out to the number of lease trackers in the region, not select which ones to average.

    I would try =AVG(COLLECT({Lease Tracker 2018 Lease Rate Offer in $}, {Lease Tracker 2018 PE GL Checkbox}, 1, {Lease Tracker 2018 Region}, "Region1"))

  • Brilliant! Thank you so much Willf!!!

  • Willf,

     

    I have another question for you if you have a second.  I have three regions and, for now, only two regions have leases in them.  The third gives me the #DIVIDE BY ZERO in the cell.  How do I make it so it's a 0 or stays blank instead of a formula error?

     

    Kindly,

     

    KO

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    You would use an IFERROR function to account for that and other errors.

    Please see the attached link/screenshot for more information.

    https://help.smartsheet.com/function/iferror

    I hope this helps you!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andree,

     

    Thank you for your response.  However, I've looked at that link and its an almost explanation for me.  Is the entire equation started with iferror or do I add to the end?

     

    =AVG(COLLECT({Lease Tracker 2018 Lease Rate Offer in $}, {Lease Tracker 2018 PE GL Checkbox}, 1, {Lease Tracker 2018 Region}, $Region3),IFERROR())

     

    Thanks ahead of time for assistance!

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    You'd add the IFERROR in the beginning and the Error message in the end.

    Try this.

    =IFERROR(AVG(COLLECT({Lease Tracker 2018 Lease Rate Offer in $}, {Lease Tracker 2018 PE GL Checkbox}, 1, {Lease Tracker 2018 Region}, $Region3)),"Error Message")

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You could also add in some criteria to the COLLECT statement to only pull cells that have numbers in them.

     

    =AVG(COLLECT({Lease Tracker 2018 Lease Rate Offer in $}, @{Lease Tracker 2018 Lease Rate Offer in $}, ISNUMBER(@cell), {Lease Tracker 2018 PE GL Checkbox}, 1, {Lease Tracker 2018 Region}, "Region1"))

    thinkspi.com

  • Andree, this is perfect.  Thanks a million!

     

  • Paul,

     

    This, too, is excellent advice.  Appreciate the comment!

     

    KO

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Great!

    Happy to help!

    Have a fantastic weekend!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.