Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif questions
I would like to create a function to sum my wins (W) and Losses (L) and
calculate winning percentage. So, if I have a column (J6:J40) that is a mix of W's and L's. I would like to sum: wins in cell K6, losses in cell k7, and winning percentage in cell k8. Is the sumif even the right function? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif questions
Adding another criteria. Each win or loss will also be classified as these
designations: S, O, U, D, or P. So I'd like the function to include this column (H6:H40), which will allow me to calculate wins/losses by category found in column H. "BAS" wrote: I would like to create a function to sum my wins (W) and Losses (L) and calculate winning percentage. So, if I have a column (J6:J40) that is a mix of W's and L's. I would like to sum: wins in cell K6, losses in cell k7, and winning percentage in cell k8. Is the sumif even the right function? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif questions
Adding an example...
Column H Column J Column K Category Wins/Losses Category S S W Wins 3 S W Losses 3 S L Percentage 0.500 S W S L S L Category O O W Wins 3 O W Losses 0 O W Percentage 1.000 U L U L U W D L D W D L D W D W P L P W P W P W "BAS" wrote: Adding another criteria. Each win or loss will also be classified as these designations: S, O, U, D, or P. So I'd like the function to include this column (H6:H40), which will allow me to calculate wins/losses by category found in column H. "BAS" wrote: I would like to create a function to sum my wins (W) and Losses (L) and calculate winning percentage. So, if I have a column (J6:J40) that is a mix of W's and L's. I would like to sum: wins in cell K6, losses in cell k7, and winning percentage in cell k8. Is the sumif even the right function? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif questions
I think I'd make a little table.
I'd put S, O, U, D, P in a column (say X6:X10). Then to count the number of wins for X6 (S) I'd put this in y6: =sumproduct(--($h$6:$h$40=$x6),--($J$6:$J$40="W")) then in z6 to count the number of losses: =sumproduct(--($h$6:$h$40=$x6),--($J$6:$J$40="L")) Then in AA6 the percentage =if(sum(x6:z6)=0,"",y6/(y6+z6)) (give this a nice number format) And these three formulas down. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html BAS wrote: Adding an example... Column H Column J Column K Category Wins/Losses Category S S W Wins 3 S W Losses 3 S L Percentage 0.500 S W S L S L Category O O W Wins 3 O W Losses 0 O W Percentage 1.000 U L U L U W D L D W D L D W D W P L P W P W P W "BAS" wrote: Adding another criteria. Each win or loss will also be classified as these designations: S, O, U, D, or P. So I'd like the function to include this column (H6:H40), which will allow me to calculate wins/losses by category found in column H. "BAS" wrote: I would like to create a function to sum my wins (W) and Losses (L) and calculate winning percentage. So, if I have a column (J6:J40) that is a mix of W's and L's. I would like to sum: wins in cell K6, losses in cell k7, and winning percentage in cell k8. Is the sumif even the right function? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
2 questions | New Users to Excel | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
2 questions | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |