Lookup tables?
 

I have 5 plus variables. See below for types of criteria. If the criteria
meet certain conditions then it needs to sum all the appropriate values for
that case:

One criteria could be risk, another size and a third time.

Col A Col B Col C
Risk Value Weight
Row 1 1 1 0.9
Row 2 2 0
Row 3 3 0
Row 4 4 0

Col D Col E Col F
Time Value Weight
Row 1 Short 0
Row 2 Med 0.9 0.95
Row 3 Long 0

Col G Col H Col I
Size Value Weight
Row 1 Small 1.2 0.8
Row 2 Med 0
Row 3 Large 0

A B C
Case A: Risk Time Size
Row5 1 Short Small

So in this case it would get 1*(0.9/(0.9+0.8)) for risk and
1.2*(0.8/(0.9+0.8)) for size = number. What is the most efficient way to do
this?

Thank you

Lookup tables?
 

Dave,
Adding an extra column (J) for the sum of the weights:

With data in row 9 (headings row 8)

Risk Value:

=SUMPRODUCT(--($A$2:$A$5=A9),($B$2:$B$5),($C$2:$C$5))/SUMPRODUCT(--($A$2:$A$5=A9),($J$2:$J$5))

Time Value:

=SUMPRODUCT(--($D$2:$D$5=B9),($E$2:$E$5),($F$2:$F$5))/SUMPRODUCT(--($D$2:$D$5=B9),($J$2:$J$5))

Size value:

=SUMPRODUCT(--($G$2:$G$5=C9),($H$2:$H$5),($I$2:$I$5))/SUMPRODUCT(--($G$2:$G$5=C9),($J$2:$J$5))

I make no claim that this is the most efficient way!

An alternative solution is using VLOOKUP:

For risk:

=VLOOKUP(A$9,A$2:C$5,2)*VLOOKUP(A$9,A$2:C$5,3)/((OFFSET($A$2,MATCH(A$9,A$2:A$5,0)-1,9)))

Change ranges for other two parameters

HTH

"Dave" wrote:

I have 5 plus variables. See below for types of criteria. If the criteria
meet certain conditions then it needs to sum all the appropriate values for
that case:

One criteria could be risk, another size and a third time.

Col A Col B Col C
Risk Value Weight
Row 1 1 1 0.9
Row 2 2 0
Row 3 3 0
Row 4 4 0

Col D Col E Col F
Time Value Weight
Row 1 Short 0
Row 2 Med 0.9 0.95
Row 3 Long 0

Col G Col H Col I
Size Value Weight
Row 1 Small 1.2 0.8
Row 2 Med 0
Row 3 Large 0

A B C
Case A: Risk Time Size
Row5 1 Short Small

So in this case it would get 1*(0.9/(0.9+0.8)) for risk and
1.2*(0.8/(0.9+0.8)) for size = number. What is the most efficient way to do
this?

Thank you

Lookup tables?
 

Toppers, sorry for the delay but I want to thank you for your input!

"Toppers" wrote:

Dave,
Adding an extra column (J) for the sum of the weights:

With data in row 9 (headings row 8)

Risk Value:

=SUMPRODUCT(--($A$2:$A$5=A9),($B$2:$B$5),($C$2:$C$5))/SUMPRODUCT(--($A$2:$A$5=A9),($J$2:$J$5))

Time Value:

=SUMPRODUCT(--($D$2:$D$5=B9),($E$2:$E$5),($F$2:$F$5))/SUMPRODUCT(--($D$2:$D$5=B9),($J$2:$J$5))

Size value:

=SUMPRODUCT(--($G$2:$G$5=C9),($H$2:$H$5),($I$2:$I$5))/SUMPRODUCT(--($G$2:$G$5=C9),($J$2:$J$5))

I make no claim that this is the most efficient way!

An alternative solution is using VLOOKUP:

For risk:

=VLOOKUP(A$9,A$2:C$5,2)*VLOOKUP(A$9,A$2:C$5,3)/((OFFSET($A$2,MATCH(A$9,A$2:A$5,0)-1,9)))

Change ranges for other two parameters

HTH

"Dave" wrote:

I have 5 plus variables. See below for types of criteria. If the criteria
meet certain conditions then it needs to sum all the appropriate values for
that case:

One criteria could be risk, another size and a third time.

Col A Col B Col C
Risk Value Weight
Row 1 1 1 0.9
Row 2 2 0
Row 3 3 0
Row 4 4 0

Col D Col E Col F
Time Value Weight
Row 1 Short 0
Row 2 Med 0.9 0.95
Row 3 Long 0

Col G Col H Col I
Size Value Weight
Row 1 Small 1.2 0.8
Row 2 Med 0
Row 3 Large 0

A B C
Case A: Risk Time Size
Row5 1 Short Small

So in this case it would get 1*(0.9/(0.9+0.8)) for risk and
1.2*(0.8/(0.9+0.8)) for size = number. What is the most efficient way to do
this?

Thank you