Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Formula Help
uWhat formula do I use to average columns, only if the values entered are
greater than zero? For instance, column one is 25, column two is 27, and column three is 0. The answer would be 26. I want the formula to exclude any entries unless they have a value of at least 1. AND, if there is only one value; ie column one is 25, but two and three are both 0, then 25 would be the answer in the final column. Example below: Subordinate Evaluation Scores Rating 1 Rating 2 Score Worker 1 25 27 26 - would be the answer I want Worker 2 25 0 25 - would be the answer Worker 3 0 0 0 - would be the answer Worker 4 0 0 Worker 5 0 0 Worker 6 1 2 1.5 would be the answer Worker 7 0 0 Worker 8 0 0 Worker 9 0 0 Worker 10 0 0 Worker 11 0 0 Average of All ? - answer would add all of column 3 and average... Thanks for any help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Formula Help
Have you tried average?
=IF(AVERAGE(L2:M2)=0,"",AVERAGE(L2:M2)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Mrs A" <Mrs wrote in message ... uWhat formula do I use to average columns, only if the values entered are greater than zero? For instance, column one is 25, column two is 27, and column three is 0. The answer would be 26. I want the formula to exclude any entries unless they have a value of at least 1. AND, if there is only one value; ie column one is 25, but two and three are both 0, then 25 would be the answer in the final column. Example below: Subordinate Evaluation Scores Rating 1 Rating 2 Score Worker 1 25 27 26 - would be the answer I want Worker 2 25 0 25 - would be the answer Worker 3 0 0 0 - would be the answer Worker 4 0 0 Worker 5 0 0 Worker 6 1 2 1.5 would be the answer Worker 7 0 0 Worker 8 0 0 Worker 9 0 0 Worker 10 0 0 Worker 11 0 0 Average of All ? - answer would add all of column 3 and average... Thanks for any help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Formula Help
try this
=IF((A1:B1)=0,LARGE(A1:B1,1),AVERAGE(A1:B1)) array formula , after entering formula Ctrl + Shift + Enter On Jun 12, 10:13*pm, Mrs A <Mrs wrote: uWhat formula do I use to average columns, only if the values entered are greater than zero? *For instance, column one is 25, column two is 27, and column three is 0. *The answer would be 26. *I want the formula to exclude any entries unless they have a value of at least 1. *AND, if there is only one value; ie column one is 25, but two and three are both 0, then 25 would be the answer in the final column. *Example below: Subordinate Evaluation Scores * * * * * * * * * * * * * Rating 1 * * * *Rating 2 * * * *Score Worker 1 * * * *25 * * *27 * * *26 - would be the answer I want Worker 2 * * * *25 * * *0 * * * 25 - would be the answer Worker 3 * * * *0 * * * 0 * * * 0 - would be the answer Worker 4 * * * *0 * * * 0 * * * Worker 5 * * * *0 * * * 0 * * * Worker 6 * * * *1 * * * 2 * * * 1.5 would be the answer Worker 7 * * * *0 * * * 0 * * * Worker 8 * * * *0 * * * 0 * * * Worker 9 * * * *0 * * * 0 * * * Worker 10 * * * 0 * * * 0 * * * Worker 11 * * * 0 * * * 0 * * * Average of All * * * * * ? - answer would add all of column 3 and average... Thanks for any help! * * |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Formula Help
One addition to my formula... because I relized that if they were all zeros,
then you would get an error. Use this modified version. =IF(SUM(A2:C2)=0,0,SUM(A2:C2)/(3-COUNTIF(A2:C2,0))) HTH, Paul -- "PCLIVE" wrote in message ... I'm sure there are other ways, but one way: =SUM(A2:C2)/(3-COUNTIF(A2:C2,0)) Does that help? Paul -- "Mrs A" <Mrs wrote in message ... uWhat formula do I use to average columns, only if the values entered are greater than zero? For instance, column one is 25, column two is 27, and column three is 0. The answer would be 26. I want the formula to exclude any entries unless they have a value of at least 1. AND, if there is only one value; ie column one is 25, but two and three are both 0, then 25 would be the answer in the final column. Example below: Subordinate Evaluation Scores Rating 1 Rating 2 Score Worker 1 25 27 26 - would be the answer I want Worker 2 25 0 25 - would be the answer Worker 3 0 0 0 - would be the answer Worker 4 0 0 Worker 5 0 0 Worker 6 1 2 1.5 would be the answer Worker 7 0 0 Worker 8 0 0 Worker 9 0 0 Worker 10 0 0 Worker 11 0 0 Average of All ? - answer would add all of column 3 and average... Thanks for any help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|