Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula
I need a formula to sum a conditional array, If A is 1 and B is 1 SUM the
cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example I have A part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D values 8.911, 9.058, 9.106, 9.031. A B C D 1 1 1 8.911 1 1 2 9.058 1 1 3 9.106 1 1 4 9.031 1 2 1 9.883 1 2 2 9.292 1 2 3 9.793 1 2 4 9.353 1 3 1 9.491 1 3 2 9.731 1 3 3 9.057 1 3 4 9.304 2 1 1 13.632 2 1 2 13.827 2 1 3 13.184 2 1 4 13.532 2 2 1 15.32 2 2 2 15.037 2 2 3 14.884 2 2 4 15.31 2 3 1 16.317 2 3 2 16.48 2 3 3 16.31 2 3 4 16.256 -- tsterople |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula
Try this:
=SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) -- Biff Microsoft Excel MVP "tsterople" wrote in message ... I need a formula to sum a conditional array, If A is 1 and B is 1 SUM the cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example I have A part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D values 8.911, 9.058, 9.106, 9.031. A B C D 1 1 1 8.911 1 1 2 9.058 1 1 3 9.106 1 1 4 9.031 1 2 1 9.883 1 2 2 9.292 1 2 3 9.793 1 2 4 9.353 1 3 1 9.491 1 3 2 9.731 1 3 3 9.057 1 3 4 9.304 2 1 1 13.632 2 1 2 13.827 2 1 3 13.184 2 1 4 13.532 2 2 1 15.32 2 2 2 15.037 2 2 3 14.884 2 2 4 15.31 2 3 1 16.317 2 3 2 16.48 2 3 3 16.31 2 3 4 16.256 -- tsterople |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula
OUT F#*%KING STANDING BIFF! I try very hard to answer my own questions and
have labored over this one for weeks, thank you so much...can you elaborate on the notational format,(--)? -- tsterople "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) -- Biff Microsoft Excel MVP "tsterople" wrote in message ... I need a formula to sum a conditional array, If A is 1 and B is 1 SUM the cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example I have A part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D values 8.911, 9.058, 9.106, 9.031. A B C D 1 1 1 8.911 1 1 2 9.058 1 1 3 9.106 1 1 4 9.031 1 2 1 9.883 1 2 2 9.292 1 2 3 9.793 1 2 4 9.353 1 3 1 9.491 1 3 2 9.731 1 3 3 9.057 1 3 4 9.304 2 1 1 13.632 2 1 2 13.827 2 1 3 13.184 2 1 4 13.532 2 2 1 15.32 2 2 2 15.037 2 2 3 14.884 2 2 4 15.31 2 3 1 16.317 2 3 2 16.48 2 3 3 16.31 2 3 4 16.256 -- tsterople |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula
=SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20)
These expressions will return an array of either TRUE or FALSE: (A1:A20=1) (B1:B20=1) The "--" coerces the TRUE and FALSE to either 1 or 0. Then, all 3 arrays are multiplied together then summed for the result. Based on the first few cells in your range it would look like this: 1*1*8.911 = 8.911 1*1*9.058 = 9.058 1*1*9.106 = 9.106 1*1*9.031 = 9.031 1*0*9.883 = 0 =SUMPRODUCT({8.911;9.058;9.106;9.031;0}) = 36.106 -- Biff Microsoft Excel MVP "tsterople" wrote in message ... OUT F#*%KING STANDING BIFF! I try very hard to answer my own questions and have labored over this one for weeks, thank you so much...can you elaborate on the notational format,(--)? -- tsterople "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) -- Biff Microsoft Excel MVP "tsterople" wrote in message ... I need a formula to sum a conditional array, If A is 1 and B is 1 SUM the cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example I have A part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D values 8.911, 9.058, 9.106, 9.031. A B C D 1 1 1 8.911 1 1 2 9.058 1 1 3 9.106 1 1 4 9.031 1 2 1 9.883 1 2 2 9.292 1 2 3 9.793 1 2 4 9.353 1 3 1 9.491 1 3 2 9.731 1 3 3 9.057 1 3 4 9.304 2 1 1 13.632 2 1 2 13.827 2 1 3 13.184 2 1 4 13.532 2 2 1 15.32 2 2 2 15.037 2 2 3 14.884 2 2 4 15.31 2 3 1 16.317 2 3 2 16.48 2 3 3 16.31 2 3 4 16.256 -- tsterople |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula
Are there an infinite number of possible arrays, infinite iterative?
-- tsterople "T. Valko" wrote: =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) These expressions will return an array of either TRUE or FALSE: (A1:A20=1) (B1:B20=1) The "--" coerces the TRUE and FALSE to either 1 or 0. Then, all 3 arrays are multiplied together then summed for the result. Based on the first few cells in your range it would look like this: 1*1*8.911 = 8.911 1*1*9.058 = 9.058 1*1*9.106 = 9.106 1*1*9.031 = 9.031 1*0*9.883 = 0 =SUMPRODUCT({8.911;9.058;9.106;9.031;0}) = 36.106 -- Biff Microsoft Excel MVP "tsterople" wrote in message ... OUT F#*%KING STANDING BIFF! I try very hard to answer my own questions and have labored over this one for weeks, thank you so much...can you elaborate on the notational format,(--)? -- tsterople "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) -- Biff Microsoft Excel MVP "tsterople" wrote in message ... I need a formula to sum a conditional array, If A is 1 and B is 1 SUM the cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example I have A part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D values 8.911, 9.058, 9.106, 9.031. A B C D 1 1 1 8.911 1 1 2 9.058 1 1 3 9.106 1 1 4 9.031 1 2 1 9.883 1 2 2 9.292 1 2 3 9.793 1 2 4 9.353 1 3 1 9.491 1 3 2 9.731 1 3 3 9.057 1 3 4 9.304 2 1 1 13.632 2 1 2 13.827 2 1 3 13.184 2 1 4 13.532 2 2 1 15.32 2 2 2 15.037 2 2 3 14.884 2 2 4 15.31 2 3 1 16.317 2 3 2 16.48 2 3 3 16.31 2 3 4 16.256 -- tsterople |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula
In versions prior to Excel 2007 Sumproduct can have up to 30 arguments. In
Excel 2007 the number of arguments was increased to 64 (I think it was 64). See this for a comprehensive explanation of Sumproduct: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "tsterople" wrote in message ... Are there an infinite number of possible arrays, infinite iterative? -- tsterople "T. Valko" wrote: =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) These expressions will return an array of either TRUE or FALSE: (A1:A20=1) (B1:B20=1) The "--" coerces the TRUE and FALSE to either 1 or 0. Then, all 3 arrays are multiplied together then summed for the result. Based on the first few cells in your range it would look like this: 1*1*8.911 = 8.911 1*1*9.058 = 9.058 1*1*9.106 = 9.106 1*1*9.031 = 9.031 1*0*9.883 = 0 =SUMPRODUCT({8.911;9.058;9.106;9.031;0}) = 36.106 -- Biff Microsoft Excel MVP "tsterople" wrote in message ... OUT F#*%KING STANDING BIFF! I try very hard to answer my own questions and have labored over this one for weeks, thank you so much...can you elaborate on the notational format,(--)? -- tsterople "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) -- Biff Microsoft Excel MVP "tsterople" wrote in message ... I need a formula to sum a conditional array, If A is 1 and B is 1 SUM the cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example I have A part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D values 8.911, 9.058, 9.106, 9.031. A B C D 1 1 1 8.911 1 1 2 9.058 1 1 3 9.106 1 1 4 9.031 1 2 1 9.883 1 2 2 9.292 1 2 3 9.793 1 2 4 9.353 1 3 1 9.491 1 3 2 9.731 1 3 3 9.057 1 3 4 9.304 2 1 1 13.632 2 1 2 13.827 2 1 3 13.184 2 1 4 13.532 2 2 1 15.32 2 2 2 15.037 2 2 3 14.884 2 2 4 15.31 2 3 1 16.317 2 3 2 16.48 2 3 3 16.31 2 3 4 16.256 -- tsterople |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula
"ab3d4u" wrote in message
... T. Valko;2499598 Wrote: In versions prior to Excel 2007 Sumproduct can have up to 30 arguments. In Excel 2007 the number of arguments was increased to 64 (I think it was 64). See this for a comprehensive explanation of Sumproduct: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "tsterople" wrote in message ...- Are there an infinite number of possible arrays, infinite iterative? -- tsterople "T. Valko" wrote: - =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) These expressions will return an array of either TRUE or FALSE: (A1:A20=1) (B1:B20=1) The "--" coerces the TRUE and FALSE to either 1 or 0. Then, all 3 arrays are multiplied together then summed for the result. Based on the first few cells in your range it would look like this: 1*1*8.911 = 8.911 1*1*9.058 = 9.058 1*1*9.106 = 9.106 1*1*9.031 = 9.031 1*0*9.883 = 0 =SUMPRODUCT({8.911;9.058;9.106;9.031;0}) = 36.106 -- Biff Microsoft Excel MVP "tsterople" wrote in message ...- OUT F#*%KING STANDING BIFF! I try very hard to answer my own questions and have labored over this one for weeks, thank you so much...can you elaborate on the notational format,(--)? -- tsterople "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A20=1),--(B1:B20=1),D1:D20) -- Biff Microsoft Excel MVP "tsterople" wrote in message ... I need a formula to sum a conditional array, If A is 1 and B is 1 SUM the cooresponding D values 8.911, 9.058, 9.106, 9.031. In this example I have A part 1, B Appraiser 1, C number of trials i.e. 1, 2, 3, 4 and D values 8.911, 9.058, 9.106, 9.031. A B C D 1 1 1 8.911 1 1 2 9.058 1 1 3 9.106 1 1 4 9.031 1 2 1 9.883 1 2 2 9.292 1 2 3 9.793 1 2 4 9.353 1 3 1 9.491 1 3 2 9.731 1 3 3 9.057 1 3 4 9.304 2 1 1 13.632 2 1 2 13.827 2 1 3 13.184 2 1 4 13.532 2 2 1 15.32 2 2 2 15.037 2 2 3 14.884 2 2 4 15.31 2 3 1 16.317 2 3 2 16.48 2 3 3 16.31 2 3 4 16.256 -- tsterople - -- I do not know if I am posting in the right spot. I have an icon between the last part of the formula D1 and D2. How do I get rid of it? What sign/function does this icon represent? Muchas gracias -- ab3d4u Sorry, I have no idea what you're talking about. A wild guess is that html might interpret this string ":D" as a "smilie". Other than that, I got nothin! -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formula | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula, Conditional Formula Needed | Excel Discussion (Misc queries) | |||
Conditional Formula to indicate Formula in cell | New Users to Excel | |||
Conditional formula? | Excel Discussion (Misc queries) |