Sumproduct with trailing amount, ranking spreadsheet

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,065
Office Version
  1. 365
Platform
  1. Windows
folks, anyone shed some light on why you woud add a trailing amount to a sumproduct formula? background: I am redoing a tender evaluation spreadsheet built a while ago and when it comes to the weighting of each respondents criteria, the person who built the spreadsheet has used a sumproduct formula plus trailing amount.

for example, with 10 respondents, the tenth respondents weightings are calculated like this:

SUMPRODUCT($C$30:$C$37,CF30:CF37)+0.00001 (where the criteria weighting is in C and the respondents weighting of that criteria is in CF)

the ninth is

SUMPRODUCT($C$30:$C$37,BX30:BX37)+0.000011

eighth: SUMPRODUCT($C$30:$C$37,BP30:BP37)+0.0000111

7th SUMPRODUCT($C$30:$C$37,BH30:BH37)+0.00001111

back to the first : SUMPRODUCT($C$30:$C$37,D30:D37)+0.00001111111111

The only thing i can think of is that it will keep the order as originally entered if all respondents score exactly the same.

anyone else knowledgeable in this area?
 
Sort by date Sort by votes
ajm,
Like you all i can think of is that there is an unofficial benfit for being the first responder. It may be a telling sign, that the trailing amount is less than 0.00001. It would seem that the author wanted to hide this fact if the displayed value is 4 decimal places or less.
Maybe they just wanted to have a consistent rank/sort where there were equal scores. First past the post is as good a reason as any.

This trailing amount may also be used to mask 'small' values created by the weighting process eg 1E-08 and avoid spurious ranking changes where the differnce in score was not significant.
 
Upvote 0
kieran,

here is the thing, even with the difference between each one, I cannot find why i get the same rank for first and second respondents.


Excel 2007
BCDEFGHIJKLMNOPQRS
24ab
25Mandatory RequirementsCriteria weightingSelect Evaluation memberCommentsSelect Evaluation memberComments
26  Select optionSelect option
27  Select optionSelect option
28  Select optionSelect option
29Evaluation criteriaCriteria weighting (%)Select Evaluation memberCommentsSelect Evaluation memberComments
30Contractors Expertise40%Select option
31Contractors Capability15%Select option
32Project Methodology30%Select option
33Safe Work Practices15%Select option
34  Select option
35  Select option
36  
37  
38Total weighted score100%0.00.0
39
40PassPass
411a0.0PassaYes11a1a1.1111111111111100000000000E-05
421b0.0Pass Yes1X 2c1.1111111111111100000000000E-05
433c0.0PasscYes22c3d1.1111111111111000000000000E-05
444d0.0PassdYes33d4e1.1111111111110000000000000E-05
455e0.0PasseYes44e5f1.1111111111100000000000000E-05
2. Individual evaluation
Cell Formulas
RangeFormula
D24=IF('1. Definitions'!$B53="","",'1. Definitions'!$B53)
D25=B14
D29=D25
D38=SUMPRODUCT($C$30:$C$37,D30:D37)+0.0000111111111111111
D41=IF('1. Definitions'!B53="","",'1. Definitions'!B53)
D42=IF('1. Definitions'!B54="","",'1. Definitions'!B54)
D43=IF('1. Definitions'!B55="","",'1. Definitions'!B55)
D44=IF('1. Definitions'!B56="","",'1. Definitions'!B56)
D45=IF('1. Definitions'!B57="","",'1. Definitions'!B57)
L24=IF('1. Definitions'!B54="","",'1. Definitions'!B54)
L25=$D$25
L29=$D$25
L38=SUMPRODUCT($C$30:$C$37,L30:L37)+0.0000111111111111111
B26=IF('1. Definitions'!B26="","",IF('1. Definitions'!B26="Insert other mandatory criteria here","",'1. Definitions'!B26))
B27=IF('1. Definitions'!B27="","",IF('1. Definitions'!B27="Insert other mandatory criteria here","",'1. Definitions'!B27))
B28=IF('1. Definitions'!B28="","",IF('1. Definitions'!B28="Insert other mandatory criteria here","",'1. Definitions'!B28))
B30=IF('1. Definitions'!B30="","",IF('1. Definitions'!B30="Insert other mandatory criteria here","",'1. Definitions'!B30))
B31=IF('1. Definitions'!B31="","",IF('1. Definitions'!B31="Insert other mandatory criteria here","",'1. Definitions'!B31))
B32=IF('1. Definitions'!B32="","",IF('1. Definitions'!B32="Insert other mandatory criteria here","",'1. Definitions'!B32))
B33=IF('1. Definitions'!B33="","",IF('1. Definitions'!B33="Insert other mandatory criteria here","",'1. Definitions'!B33))
B34=IF('1. Definitions'!B34="","",IF('1. Definitions'!B34="Insert other mandatory criteria here","",'1. Definitions'!B34))
B35=IF('1. Definitions'!B35="","",IF('1. Definitions'!B35="Insert other mandatory criteria here","",'1. Definitions'!B35))
B36=IF('1. Definitions'!B36="","",IF('1. Definitions'!B36="Insert other mandatory criteria here","",'1. Definitions'!B36))
B37=IF('1. Definitions'!B37="","",IF('1. Definitions'!B37="Insert other non price evaluation criteria here","",'1. Definitions'!B37))
C26=IF(B26="","",'1. Definitions'!C26)
C27=IF(B27="","",'1. Definitions'!C27)
C28=IF(B28="","",'1. Definitions'!C28)
C30=IF('1. Definitions'!C30="","",'1. Definitions'!C30)
C31=IF('1. Definitions'!C31="","",'1. Definitions'!C31)
C32=IF('1. Definitions'!C32="","",'1. Definitions'!C32)
C33=IF('1. Definitions'!C33="","",'1. Definitions'!C33)
C34=IF('1. Definitions'!C34="","",'1. Definitions'!C34)
C35=IF('1. Definitions'!C35="","",'1. Definitions'!C35)
C36=IF('1. Definitions'!C36="","",'1. Definitions'!C36)
C37=IF('1. Definitions'!C37="","",'1. Definitions'!C37)
C38=SUM(C30:C37)
C41=RANK(E41,$E$41:$E$56,0)
C42=RANK(E42,$E$41:$E$56,0)
C43=RANK(E43,$E$41:$E$56,0)
C44=RANK(E44,$E$41:$E$56,0)
C45=RANK(E45,$E$41:$E$56,0)
K40=IF(AND(D26<>"Fail",D27<>"Fail",D28<>"Fail"),"Pass","Fail")
K41=IF(H41="Yes",G41,"")
K42=IF(H42="Yes",G42,"")
K43=IF(H43="Yes",G43,"")
K44=IF(H44="Yes",G44,"")
K45=IF(H45="Yes",G45,"")
S40=IF(AND(L26<>"Fail",L27<>"Fail",L28<>"Fail"),"Pass","Fail")
E41=IF($D41="","",INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D41,$D$24:$EA$24,0)))
E42=IF($D42="","",INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D42,$D$24:$EA$24,0)))
E43=IF($D43="","",INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D43,$D$24:$EA$24,0)))
E44=IF($D44="","",INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D44,$D$24:$EA$24,0)))
E45=IF($D45="","",INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D45,$D$24:$EA$24,0)))
F41=IF($D41="","",INDEX($D$24:$EA$40,ROW($K$40)-ROW($D$24)+1,MATCH($D41,$D$24:$EA$24,0)+7))
F42=IF($D42="","",INDEX($D$24:$EA$40,ROW($K$40)-ROW($D$24)+1,MATCH($D42,$D$24:$EA$24,0)+7))
F43=IF($D43="","",INDEX($D$24:$EA$40,ROW($K$40)-ROW($D$24)+1,MATCH($D43,$D$24:$EA$24,0)+7))
F44=IF($D44="","",INDEX($D$24:$EA$40,ROW($K$40)-ROW($D$24)+1,MATCH($D44,$D$24:$EA$24,0)+7))
F45=IF($D45="","",INDEX($D$24:$EA$40,ROW($K$40)-ROW($D$24)+1,MATCH($D45,$D$24:$EA$24,0)+7))
G41=B60
G42=B61
G43=B62
G44=B63
G45=B64
H41=IF(D60<>"Fail",E60,"No")
H42=IF(D61<>"Fail",E61,"No")
H43=IF(D62<>"Fail",E62,"No")
H44=IF(D63<>"Fail",E63,"No")
H45=IF(D64<>"Fail",E64,"No")
I41=IF(K41<>"",H39+1,H39)
I42=IF(K42<>"",I41+1,I41)
I43=IF(K43<>"",I42+1,I42)
I44=IF(K44<>"",I43+1,I43)
I45=IF(K45<>"",I44+1,I44)
J41=IF(I41=H39,"X",I41)
J42=IF(I42=I41,"X",I42)
J43=IF(I43=I42,"X",I43)
J44=IF(I44=I43,"X",I44)
J45=IF(I45=I44,"X",I45)
M41=IF(ISERROR(VLOOKUP(L41,$J$41:$K$56,2,0)),"",VLOOKUP(L41,$J$41:$K$56,2,0))
M42=IF(ISERROR(VLOOKUP(L42,$J$41:$K$56,2,0)),"",VLOOKUP(L42,$J$41:$K$56,2,0))
M43=IF(ISERROR(VLOOKUP(L43,$J$41:$K$56,2,0)),"",VLOOKUP(L43,$J$41:$K$56,2,0))
M44=IF(ISERROR(VLOOKUP(L44,$J$41:$K$56,2,0)),"",VLOOKUP(L44,$J$41:$K$56,2,0))
M45=IF(ISERROR(VLOOKUP(L45,$J$41:$K$56,2,0)),"",VLOOKUP(L45,$J$41:$K$56,2,0))
O41=INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D41,$D$24:$EA$24,0))
O42=INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D42,$D$24:$EA$24,0))
O43=INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D43,$D$24:$EA$24,0))
O44=INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D44,$D$24:$EA$24,0))
O45=INDEX($D$24:$EA$38,ROW($D$38)-ROW($D$24)+1,MATCH($D45,$D$24:$EA$24,0))


working backwards, the ranking is where i am getting problems now. assuming that the trailing figures serve a valid purpose, with a blank scoring section, the respondents should be ranked 1-16 ( ia dded one from the original post). my first two are both ranked one. ??? if you look at cells C41 and C42, the ranking formula is set jup correctly, so my problem has to be with the formula in E41 and E42. jump over to column O and the result of the trailing number is in O41 and O42.

Notice both are the same. Why? its interesting as no matter how many 1's i add to the backend of either number, I still get this answer. also, taking out a zero also doesn't change the number.

hmm, whats going on, i wonder
 
Upvote 0
couldn't edit. so here is my PS. take out a couple of respondents and it works fine. must be a limit to the number of decimal places excel likes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

jimrward
  • Question Question
golf score rankings
Replies
4
Views
770
Dryver14
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top