Jump to content



Photo

excel formula driving me nuts

if statement sumproduct

  • Please log in to reply
3 replies to this topic

#1 capr

capr

    Neowinian Senior

  • Joined: 01-July 05

Posted 29 August 2013 - 03:51

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?

 




#2 heatlesssun

heatlesssun

    Neowinian

  • Joined: 31-May 12

Posted 29 August 2013 - 04:14

I think this should help you out: http://www.ozgrid.co...ithout-zero.htm



#3 OP capr

capr

    Neowinian Senior

  • Joined: 01-July 05

Posted 29 August 2013 - 05:21

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? 



#4 OP capr

capr

    Neowinian Senior

  • Joined: 01-July 05

Posted 29 August 2013 - 05:28

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