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

Based on frequency selected change the formula for the created date.
I feel like this should be super simple but I am just not getting it right. Based on Frequency: Daily, weekly, monthly I want to change the next due date This will be calulated from Created column and +1, 7, 30 etc added. It works single instance =IF(Frequency@row = "Daily", Created@row + 1) but as soon as I try to write…
Counting tasks completed on time
I need a formula that says, if the "Complete?" box is checked, was the date in the Actual Completion column on (or before) the date in the Finish column.
Summary sheet values don't update in dashboard without entering source sheet?
I have a sheet summary report on a dashboard and oftentimes the values will not update unless I go into the source sheet and manually save the updates. For example, the WEEK TIME OFF value often will not update week-to-week unless I go into that contact's sheet and save. I have tried adding a daily Chron automation…