# excel formula driving me nuts

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!