Average of lease $ amounts in a certain region

KO_in_Colo

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?

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,

 

KO

 

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!

 

KO

 

Paul Newcome

Happy to help! 

Andrée Starå

Great!

Happy to help!

Have a fantastic weekend!

Best,

Andrée