capr Posted August 29, 2013 Share Posted August 29, 2013 ok so lets start with a simple sum product =SUMPRODUCT($G3:I3,$G4:I4) <--simple sum product 2 different rows keeping the starting point the same. Lets flip the second row and make it do things in reverse. simple enough (ok not so much) =SUMPRODUCT($G3:I3,N(OFFSET($G4:I4,0,COLUMNS($G4:I4)-COLUMN($G4:I4)+CELL("Col",$G4:I4)-1)))/SUM($G3:I3) NOW comes the kicker, lets make it only consider non zero values.... (get ready! ) =SUMPRODUCT(IF($G17:I17,$G17:I17),IF(N(OFFSET($G18:I18,0,COLUMNS($G18:I18)-COLUMN($G18:I18)+CELL("Col",$G18:I18)-1)),N(OFFSET($G18:I18,0,COLUMNS($G18:I18)-COLUMN($G18:I18)+CELL("Col",$G18:I18)-1))))/SUM(IF($G17:I17,$G17:I17)) it's not working :( Basically =AVERAGE(IF(A3:A5,A3:A5)) will only average non zero values. That's what I am trying to do but I am not doing it right... 1) is there an simpler way to do this? I can't sit there explaining this horrible horrible formula 2) can you proof read it and see where I am going wrong? Link to comment Share on other sites More sharing options...
0 heatlesssun Posted August 29, 2013 Share Posted August 29, 2013 I think this should help you out: http://www.ozgrid.com/Excel/average-without-zero.htm Link to comment Share on other sites More sharing options...
0 capr Posted August 29, 2013 Author Share Posted August 29, 2013 Ok so it turns out that my formula was actually working.... so I am stupid :( problem is I want it to ignore empty cells. zeros are actually ok. No idea how to pull that one off. Any thoughts? Link to comment Share on other sites More sharing options...
0 capr Posted August 29, 2013 Author Share Posted August 29, 2013 so I modified it to =SUMPRODUCT(IF($G17:V17<>"",$G17:V17),IF(N(OFFSET($G18:V18,0,COLUMNS($G18:V18)-COLUMN($G18:V18)+CELL("Col",$G18:V18)-1))<>"",N(OFFSET($G18:V18,0,COLUMNS($G18:V18)-COLUMN($G18:V18)+CELL("Col",$G18:V18)-1))))/SUM(IF($G17:V17<>"",$G17:V17)) now it has the <>"" bits that I heard will do the magic. The formula isn't functioning as expected so it's not quit working... I basically need to sumproduct two rows but want to ignore all empty cells. in a regular sum product empty cells would just be zeros and would be ignored because of the nature of sum product.... in this case however because I am flipping one row and doing the sumproduct that way, mathematically we run into trouble if there is unintended blank cells.... Link to comment Share on other sites More sharing options...
Question
capr
ok so lets start with a simple sum product
=SUMPRODUCT($G3:I3,$G4:I4) <--simple sum product 2 different rows keeping the starting point the same.
Lets flip the second row and make it do things in reverse. simple enough (ok not so much)
=SUMPRODUCT($G3:I3,N(OFFSET($G4:I4,0,COLUMNS($G4:I4)-COLUMN($G4:I4)+CELL("Col",$G4:I4)-1)))/SUM($G3:I3)
NOW comes the kicker, lets make it only consider non zero values.... (get ready! )
=SUMPRODUCT(IF($G17:I17,$G17:I17),IF(N(OFFSET($G18:I18,0,COLUMNS($G18:I18)-COLUMN($G18:I18)+CELL("Col",$G18:I18)-1)),N(OFFSET($G18:I18,0,COLUMNS($G18:I18)-COLUMN($G18:I18)+CELL("Col",$G18:I18)-1))))/SUM(IF($G17:I17,$G17:I17))
it's not working :(
Basically =AVERAGE(IF(A3:A5,A3:A5)) will only average non zero values. That's what I am trying to do but I am not doing it right...
1) is there an simpler way to do this? I can't sit there explaining this horrible horrible formula
2) can you proof read it and see where I am going wrong?
Link to comment
Share on other sites
3 answers to this question
Recommended Posts