Hi All
Can anybody help me adjust the following SUMPRODUCT formula to ignore any blanks in column B?
I am using the formula above to calculate a weighted average, and am open to using other functions if anybody has any more efficient suggestions?=SUMPRODUCT($B$5:$B$4000,$S$5:$S$4000)/SUM($S$5:$S$4000)
Any help would be greatly appreciated
Last edited by Dgp2012; 10-21-2013 at 11:40 AM.
Hello Dgp try this =SUMPRODUCT(IF(($B$5:$B$4000)<>0,($B$5:$B$4000)*($S$5:$S$4000)/SUM($S$5:$S$4000)))
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Hi Hemesh
Thanks for your swift response.
Unfortunately this has returned the #VALUE error. I also tried to exclude empty cells this way (<>"") but no luck...
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Sample workbook attached...
Hi,
Perhaps:
=SUMPRODUCT($B$3:$B$16,--($B$3:$B$16<>0),$S$3:$S$16)/SUMPRODUCT($S$3:$S$16,--($B$3:$B$16<>0))
Regards
Fantastic! Thanks very much for your help
I made a very slight adjustment to suit the blank cells; instead of using <>0, I used <>"".
Thanks again
DGP
You're welcome.
Many years later:
I need some help!
=SUMPRODUCT($C$12:$K$12, 1/$C$13:$K$13, C19:K19)
Sum product(Weightage, Max marks, Student score)
I need it to ignore the empty cells otherwise it gives a zero answer.
Can you help? I can't seem to figure out how to adapt your previously posted solution to mine.
Thanks in advance!
Administrative Note:Originally Posted by raheeljessa
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
raheeljessa, I had the same question! Including --([array]<>0) doesn't work if you need to manipulate an array within the sumproduct calculation like you and I need to. I searched for your name in this forum but you never reposted your question.
The solution below works for me. My data is listed in columns, with blanks sorted to the bottom. My total number of blanks varies with other inputs.
Say SUMPRODUCT(1/A1:A999) ignoring blanks is what you want. First sort the blanks to the bottom either with filters or another formula. Then use:
SUMPRODUCT(1/(A1:INDEX(A1:A999,COUNT(A1:A999))))
Unfortunately if you want to use other arrays in the sumproduct, they'll need to be the same size. For example, to include B1:B999, the sumproduct will look like this:
SUMPRODUCT(1/(A1:INDEX(A1:A999,COUNT(A1:A999))),B1:INDEX(B1:B999,COUNT(A1:A999)))
Using your example and switching to lists in rows:
SUMPRODUCT($C$12:INDEX($C$12:$K$12,1,COUNTIF($C$13:$K$13,"<>0")),1/($C$13:INDEX($C$13:$K$13,1,COUNTIF($C$13:$K$13,"<>0"))),C19:INDEX($C$19:$K$19,1,COUNTIF($C$13:$K$13,"<>0")))
It's ugly but it works for me. If you can't filter the 0s to the right, I would recommend transposing to columns so you can easily work with filters. If you think of a more elegant solution please share.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks