Average of lease $ amounts in a certain region

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?

Find more posts tagged with

Construction

Sheets

Finance

Sharing and Collaboration

Formulas

Community Platform

Project Management

Comments and Conversations

Comments

Willf

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

KO_in_Colo

Brilliant! Thank you so much Willf!!!

KO_in_Colo

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,

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

KO_in_Colo

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å

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

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

KO_in_Colo

Andree, this is perfect. Thanks a million!

KO_in_Colo

Paul,

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

Paul Newcome

Happy to help!

Andrée Starå

Great!

Happy to help!

Have a fantastic weekend!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…