Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT function for two arrays. Array 1 contains text
i have one array A2 to A10 containing text (A+/A/B/C). another array C2 to
C10 contains numbers (0/1). i want to multiply each element of one array with another, replacing A+ for 2, A for 1.5, B for 1 and C for .5. Example: A B C ---------------------- A 0 A+ 1 B 0 C 1 ........................... it should give me the result: 2.5 [(replace A by 1.5*0) + (replace A+ by 2*1) + (replace B by 1*0) + (replace C by .5*1)] Please help me with this.. Regards |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT function for two arrays. Array 1 contains text
Either of these two array-entered** formulas seem to do what you want...
=SUM(B2:B10*IF(A2:A10="",0,2.5-MATCH(A2:A10,{"A+","A","B","C"},0)/2)) =SUM(B2:B10*IF(A2:A10="",0,LOOKUP(A2:A10,{"A","A+" ,"B","C"},{1.5,2,1,0.5}))) ** Commit the formula by using Ctrl+Shift+Enter, not just Enter by itself. Rick "Payal" wrote in message ... i have one array A2 to A10 containing text (A+/A/B/C). another array C2 to C10 contains numbers (0/1). i want to multiply each element of one array with another, replacing A+ for 2, A for 1.5, B for 1 and C for .5. Example: A B C ---------------------- A 0 A+ 1 B 0 C 1 .......................... it should give me the result: 2.5 [(replace A by 1.5*0) + (replace A+ by 2*1) + (replace B by 1*0) + (replace C by .5*1)] Please help me with this.. Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SUMPRODUCT with arrays | Excel Discussion (Misc queries) | |||
SUMPRODUCT with 3 arrays not working | Excel Worksheet Functions | |||
Sumproduct arrays | Excel Discussion (Misc queries) | |||
Sumproduct + Array Function? | Excel Worksheet Functions | |||
Two arrays need highlight duplicate in one of the array | Excel Worksheet Functions |