Average of lease \$ amounts in a certain region

Options
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?

• Options

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"))

• Options

Brilliant! Thank you so much Willf!!!

• Options

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

• ✭✭✭✭✭✭
Options

Hi,

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

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

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

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!

• ✭✭✭✭✭✭
Options

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 EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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"))

• Options

Andree, this is perfect.  Thanks a million!

• Options

Paul,

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

KO

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

Great!

Happy to help!

Have a fantastic weekend!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!