Calculating %'s with IF and Sum/SumIF

  • Thread starter Thread starter Billy B
  • Start date Start date
B

Billy B

I have the following formula that works correctly. The formulas is used to
calculate grades for my classroom.

=IF(COUNT(G8:P8,U8,V8)<12,"",VLOOKUP(SUM(G8:P8,U8:V8/(SUMIF(G8:P8,">0",$G$7:$P$7)+SUMIF(U8:V8,">0",$U$7:$V$7)),GradeTbl,2))

What I am trying to do is weigh the grades so the range G8:P8 constitute 40%
of the grade and the range U8:V8 constitue 60% of the grade, then using
Vlookup show the grade.

I have tried Sum((G8:P8)*.4),(U8:V8)*.6)) in the SUM(G8:P8,U8:V8 part of the
formula but get an error message. I have tried others with no success.

Is it possible to accomplish what I am trying to do with one formula?

Thank you.
 
That formula does not work correctly, it errors out on input, 'too few
arguments'. What is the real formula?
 
It seems to be a missing ), this was allowed

=IF(COUNT(G8:P8,U8,V8)<12,"",VLOOKUP(SUM(G8:P8,U8:V8/(SUMIF(G8:P8,">0",$G$7:$P$7)+SUMIF(U8:V8,">0",$U$7:$V$7))),GradeTbl,2))

but can you show some data t try it on
 
Bob Phillips said:
It seems to be a missing ), this was allowed

=IF(COUNT(G8:P8,U8,V8)<12,"",VLOOKUP(SUM(G8:P8,U8:V8/(SUMIF(G8:P8,">0",$G$7:$P$7)+SUMIF(U8:V8,">0",$U$7:$V$7))),GradeTbl,2))

but can you show some data t try it on

--

HTH

Bob
Click to expand...
Rows G H I J K L M N O P Q
R S U V W
7
8 Possible 10 10 10 10 10 10 10 10 10 10 20
20
9 Earned 9 10 10 8 10 10 8 10 7 10
16 15 =IF....

and the formula in W9, copied and pasted
=IF(COUNT(G8:P8,U8,V8)<12,"",VLOOKUP(SUM(G8:P8,U8,V8)/(SUMIF(G8:P8,">0",$G$7:$P$7)+SUMIF(U8:V8,">0",$U$7:$V$7)),GradeTbl,2))

Hope this helps....thanks again
 
Your formula refers to data in row 7, but your example shows nothing, and
what about GradeTbl?
 
I'll take a punt anyway

=IF(COUNT(G8:P8,U8,V8)<12,"",VLOOKUP((SUM(G8:P8)/SUM($G$7:$P$7))*40%+(SUM(U8+V8)/SUM($U$7:$V$7))*60%,GradeTbl,2))
 
Bob Phillips said:
I'll take a punt anyway

=IF(COUNT(G8:P8,U8,V8)<12,"",VLOOKUP((SUM(G8:P8)/SUM($G$7:$P$7))*40%+(SUM(U8+V8)/SUM($U$7:$V$7))*60%,GradeTbl,2))

--

HTH

Bob
Click to expand...
 
Bob Phillips said:
=IF(COUNT(G8:P8,U8,V8)<12,"",
VLOOKUP((SUM(G8:P8)/SUM($G$7:$P$7))*40%+
(SUM(U8+V8)/SUM($U$7:$V$7))*60%,GradeTbl,2))
Click to expand...

Just commenting on syntax, without vetting the solution....

What useful purpose does SUM serve in the context SUM(U8+V8)?

Also, reordering of terms will avoid some needless parentheses without
sacrificing readability, IMHO. To wit:

=IF(COUNT(G8:P8,U8,V8)<12, "",
VLOOKUP(40%*SUM(G8:P8)/SUM($G$7:$P$7) +
60%*(U8+V8)/SUM($U$7:$V$7),GradeTbl,2))

Note: Perhaps Bob meant to write SUM(U8,V8). Although that can serve a
useful purpose sometimes, I think it is redundant here, given the
conditional test COUNT(G8:P8,U8,V8)<12.


-----
PS: Sorry about the earlier contentless response. Clicked on the wrong
button.


----- original message -----
 
It was just a typo, I meant to write SUM(U8:V8) as I did with
SUM($U$7:$V$7). I did this deliberately so that region could be extended (to
W, X, and so on) with minimal change.
 
Bob Phillips said:
It was just a typo, I meant to write SUM(U8:V8) as I
did with SUM($U$7:$V$7). I did this deliberately so
that region could be extended (to W, X, and so on)
with minimal change.
Click to expand...

I thought as much. But then, I would have written COUNT(G8:P8,U8:V8) or
SUM(U8,V8) to be consistently extensible.


----- original message -----
 
Yeah, looking at it now, but when I built my answer that was the part I
wasn't looking at :-(
 
Bob Phillips said:
Yeah, looking at it now, but when I built my answer
that was the part I wasn't looking at :-(
Click to expand...

Been there, done that. ;-)


----- original message -----
 
Back
Top