SUMIF question

B

Billy B

I am trying to calculate a percent based on the SUMIF function using
non-continuous cells. Row 5 of the worksheet contains the possible points and
row 7 the points the student has received. Have been working at it a while
and having problems getting it to work. Below is what I have so far. Any
help would be appreciate

=IF(SUM(F7:AS7,AW7:CG7,CK7:EE7,EJ7:EX7,FB7:FS7,FW7:GW7,HB7)=0,0,SUMIF({"F7:AS7"AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"},">=0",{"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}/{"$F$5:$AS$5","$AW$5:$CG$5","$CK$5:EE$5","EJ$5:EX$5","FB$5:FS$5","FW$5:GW$5","HB$5"}))
 
B

Billy B

Bernard,

I am trying to calculate a percentage based on possible points. If a student
has three assignments A,B,C and each is worth 10 points the total possible
would be 30. But, the student has only completed assignment A and received 8
points. The formula result would be 80% because he had only done one
assignment of the three possible. The total should reflect the percent based
on completed work only (row 7) divided by the possible points (row 5) for
only the completed work. Since the range of cells in my workbook for their
homework is not continuous, I am trying to develop a formula that would do
the above. I hope that explains it OK.
 
B

Bob Phillips

Just a thought

=SUMPRODUCT(SUMIF(INDIRECT({"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}),">0"))/
SUMPRODUCT(SUMIF(INDIRECT({"F7:AS7","AW7:CG7","CK7:EE7","EJ7:EX7","FB7:FS7","FW7:GW7","HB7"}),">0",
(INDIRECT({"F5:AS5","AW5:CG5","CK5:EE5","EJ5:EX5","FB5:FS5","FW5:GW5","HB5"}))))

but how about redesigning the data?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Don Guillett

This may? help.
A macro I did recently for a similar problem. Adapt to suit

Sub averagearray()
For Each c In Array(1, 2, 3, 10, 23, 34)
If Cells(c, "a") > 4.5 Then
ms = ms + Cells(c, "a")
cc = cc + 1
End If
Next c
MsgBox ms
MsgBox cc
MsgBox ms / cc
End Sub
 
Top