4 posts in this topic

Posted

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?

 

Share this post


Link to post
Share on other sites

Posted

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 this post


Link to post
Share on other sites

Posted

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.