Create an account on Neowin to contribute and support the site.

# excel formula driving me nuts

## Recommended Posts

capr    36

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

##### Share on other sites
heatlesssun    72

##### Share on other sites
capr    36

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?

##### Share on other sites
capr    36

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