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





Click here to login or here to register to remove this ad, it's free!