Results 1 to 2 of 2

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. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    charlotte, nc
    Posts
    141

    Default 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 A house addresss
    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,996

    Default

    =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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com