G
Guest
i have a work sheet with four charts. the first chart list the current month along with the previous five months for a total of six months (each month a new sheet is used and one month drops off as a new one is added), this is column "a", column "b" is the total points possible for person 1, coulmn "c" is the total possible for person 2, and so on
in the second chart i have set it up the same as the first but enter in the total incorrect for each person for each month
in the third chart i figure a percentage of the first and second chart (ie. block a2=10 possible, block a12=1 incorrect so block a22=90%) i use the formula =((a2-a12)/a2)*100 to get the percentage. in the last chart i calculate the rolling percentage averages from the third chart, i use the following formula =a22 for the first month, =average(a22:a23) for the first and second month, =average(a22:a24) for the first three months and so on untill i have a rolling average for the hole six months. the problem i have is when i have a zero in one of the blocks in the first chart. i get #Divo/0 in the third and fourth charts. what i need is to have a formula that will skip over any zero blocks and only and only average those with a value > zero. please help!
in the second chart i have set it up the same as the first but enter in the total incorrect for each person for each month
in the third chart i figure a percentage of the first and second chart (ie. block a2=10 possible, block a12=1 incorrect so block a22=90%) i use the formula =((a2-a12)/a2)*100 to get the percentage. in the last chart i calculate the rolling percentage averages from the third chart, i use the following formula =a22 for the first month, =average(a22:a23) for the first and second month, =average(a22:a24) for the first three months and so on untill i have a rolling average for the hole six months. the problem i have is when i have a zero in one of the blocks in the first chart. i get #Divo/0 in the third and fourth charts. what i need is to have a formula that will skip over any zero blocks and only and only average those with a value > zero. please help!