• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT with (MATCH("range",{"H","M","L"},0) & remove blank cells

Monique Albrecht

Monique Albrecht

New Member
Hello Everyone, I've been trying for weeks to solve this problem using the SUMPRODUCT function for weighted averages. Basically, I'm trying to create a tool that allows me to evaluate a list of products by criteria in 3 categories using High (H), Medium (M) and Low (L). The H,M or L values need to be converted to numbers, say 1, 2 and 3 in order to use the SUMPRODUCT function to get the weighted averages. I'm trying to use the MATCH function to convert the letters to numbers but if fails if I have a blank value and I get a #NA error. I need to accommodate blank cells in case the criteria does not apply to the product in the list. So if I have 6 criteria and only 4 of them apply, then only four are used in the calculation.

Here's what kind of seemed to work: =SUMPRODUCT(MATCH(AK4:AS4,{"H","M","L"},0),AK2:AS2)/SUM(AK2:AS2)

This kind of works, but fails if I have a blank or "N/A" value. Can anyone help me or offer an amazing template ?
 
Thanks ! Here you go !
1. Score Products by weighted criteria (row 2) in 3 categories using H, M, L
2. Use MATCH function or whatever to turn H, M, L values into numeric values to calculate SUMPRODUCT function.
3. Omit any cells with null or N/A values (no H,M or L) from the SUMPRODUCT calculation so that it does not impact the score.
4. Total all Category scores for each product (column AE)

let me know if more questions.

-Monique
 

Attachments

Last edited:
Hi Monique ,

I am somewhat confused by the calculation of weighted averages.

If the data in the data range can take one of 3 options , then how are the weights to be allocated ?

For instance , suppose we take just 2 input cells ; suppose one cell has the criterion value as H , and the other has the criterion value as L.

Suppose the weights are 10 and 90 for these two cells.

What will be the weighted average result ?

We are actually calculating two weights here ; one with respect to the cells , and the other with respect to the 3 options.

Narayan
 
My understanding of SUMPRODUCT is that the function takes care of aligning the data cells range and the weighting cells range, no ? So that the data in the data range (H, M OR L) is appropriately weighted by the cells in the range above where the weights are specified. I followed the instructions for using SUMPRODUCT, but I cannot seem to combine the use of H, M or L values and also removing null values...That's my main challenge I believe unless someone thinks I have completely misinterpreted instructions for SUMPRODUCT...

-Monique
 
Hi ,

Let me explain by taking the simplest situation of 2 cells :

These two cells can take the 4 values of H , M , L or blank.

With these 4 values for each cell , there can be 16 combinations ; I assumed that the weights are the weights for the cells themselves , and not for the values that they can have. After all , the values in the two cells are chosen by means of dropdowns , so that a cell can have any of the 4 possible values. Or are you saying that the weights are assigned after the selections of the cell values have been made , and that the weights are based on the selections made ?

To take a concrete example , suppose the weights are 3 and 11 for the two cells ; what will be the result then for these 16 combinations given these 2 weights ?

Or are you saying that if a cell has had H selected , you will assign it a weight of 3 , while if it had had M selected , you might have assigned it a weight of 7 ?

These are issues of problem description , and have nothing to do with the SUMPRODUCT function.

Narayan
 
If anyone has another approach that might work, let me know. It could be that the approach I've taken is an ultimate dead end for my requirements.

Thanks,
Monique
 
Right @Chihiro

With blanks too... we have to change the formulae with this CSE:

=SUM(IFERROR(MATCH(C4:J4,{"N/A","L","M","H"},0)-1,)*C$2:J$2)/SUM(C$2:J$2*(C4:J4<>"")*(C4:J4<>"N/A"))

Blessings!
 
Chihiro said:
This formula should work.
=(SUMPRODUCT((C4:J4="H")*3*($C$2:$J$2))+SUMPRODUCT((C4:J4="M")*2*($C$2:$J$2))+SUMPRODUCT((C4:J4="L")*1*($C$2:$J$2)))/SUMPRODUCT((C4:J4<>"n/a")*(C4:J4<>"")*($C$2:$J$2))
Click to expand...


Wow, I'm pretty sure that worked !!!! I mean it seems to work, but I'm just in process to test out the results... I really, really, really appreciate your help ! I don't know how to thank you enough. This is just great :)
 
Okay, I'm making progress on my tool based on that excellent answer....However now I'm being asked to factor in the ability to "weight" the actual "evaluation categories" (in additional to the "criteria" that have already been factored in) - this should impact the TOTAL SCORE in column AG based on the weighted average of the "evaluation category" scores in columns B, L and T.

I'm going to work on some other aspects of the tool and see if anyone can help with this new add....

-Monique
 
Hello Everyone, the tool is coming along very well thanks to everyone's input here. At this point, I am now able to start creating reports based on the data on another table....however I have one more request from a colleague to add another layer of weights at the "category level" for the total scores in each category...

If you look at the attached I've added a row 3 to add the weights at the "category level" such that the individual scores in cells B5:B7, L5:L7, T5:T7, AD5:AD7 will be weighted by whats in row 3 and impact the total score in column AG.

Let me know if you have any further questions. I'm thinking its just a matter of making a reference to the %-age weighting in each category in the current function below, but I haven't figured out how to integrate it in yet....any help would be appreciated. Oh and the same constraints applies here - if there is a "0" score or weighting to factor it out of the calculation so that it doesn't impact the score....

(=(SUMPRODUCT((C5:J5="H")*3*($C$2:$J$2))+SUMPRODUCT((C5:J5="M")*2*($C$2:$J$2))+SUMPRODUCT((C5:J5="L")*1*($C$2:$J$2)))/SUMPRODUCT((C5:J5<>"n/a")*(C5:J5<>"")*($C$2:$J$2)))

Thank you !

-Monique
 

Attachments

You must log in or register to reply here.
Back
Top