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?
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
-
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 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.
-
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!
-
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.
-
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"))
-
Andree, this is perfect. Thanks a million!
-
Paul,
This, too, is excellent advice. Appreciate the comment!
KO
-
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!