Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Tables | Excel Worksheet Functions | |||
Lookup Tables and Formulas | Excel Worksheet Functions | |||
lookup tables | Excel Worksheet Functions | |||
Lookup tables | Excel Worksheet Functions | |||
lookup tables | Excel Worksheet Functions |