# Formula and approach to problem

This is a discussion on Formula and approach to problem within the Excel Questions forums, part of the Question Forums category; I have a sheet for house remodeling expenses. It is hard to explain what I am trying to do. Col ...

1. ## Formula and approach to problem

I have a sheet for house remodeling expenses. It is hard to explain what I am trying to do.
Col B has square footage
Col C starts the various expense such as cabinets then D has painting and E has Plumbing

What I am trying to do is if I have 20 houses and I get to col C for house number 1 I tell it to divide the cost for painting that house by the sqft of that house to get a per sqft cost to paint. But I need for the painting col to sum all of the painting costs for all 20 houses which it does but I want it to only divide by the number of houses that I actually painted because some I did not paint. I mean if I only needed to paint 5 of the houses then the total cost of the painting for those 5 then gets divided by 20 houses. How can I get the sheet to ingore totaling the houses that have 0.00 in the cell meaning no paint cost is associated with this house.

Thanks for any help.

Galen

2. =AVERAGE(IF(Range,Range))

which must be entered by using control+shift+enter, not just enter.

Equivalently...

=SUM(Range)/MAX(1,COUNT(Range)-COUNTIF(Range,0))

which is ordinarily entered.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•