Calculating %'s with IF and Sum/SumIF

[Billy B]

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

=IF(COUNT(G88,U8,V8)<12,"",VLOOKUP(SUM(G88,U8:V8/(SUMIF(G88,">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 G88 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((G88)*.4),(U8:V8)*.6)) in the SUM(G88,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.

 

[Bob Phillips]

That formula does not work correctly, it errors out on input, 'too few
arguments'. What is the real formula?

 

[Bob Phillips]

It seems to be a missing ), this was allowed

=IF(COUNT(G88,U8,V8)<12,"",VLOOKUP(SUM(G88,U8:V8/(SUMIF(G88,">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

 

[Billy B]

It seems to be a missing ), this was allowed

=IF(COUNT(G88,U8,V8)<12,"",VLOOKUP(SUM(G88,U8:V8/(SUMIF(G88,">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

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(G88,U8,V8)<12,"",VLOOKUP(SUM(G88,U8,V8)/(SUMIF(G88,">0",$G$7:$P$7)+SUMIF(U8:V8,">0",$U$7:$V$7)),GradeTbl,2))

Hope this helps....thanks again

 

[Bob Phillips]

Your formula refers to data in row 7, but your example shows nothing, and
what about GradeTbl?

 

[Bob Phillips]

I'll take a punt anyway

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

 

[Joe User]

I'll take a punt anyway

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

--

HTH

Bob

 

[Joe User]

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

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(G88,U8,V8)<12, "",
VLOOKUP(40%*SUM(G88)/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(G88,U8,V8)<12.


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


----- original message -----

 

[Billy B]

Thank you both,Bob & Joe. The solution works great.

 

[Bob Phillips]

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.

 

[Joe User]

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.

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


----- original message -----

 

[Bob Phillips]

Yeah, looking at it now, but when I built my answer that was the part I
wasn't looking at :-(

 

[Joe User]

Yeah, looking at it now, but when I built my answer
that was the part I wasn't looking at :-(

Been there, done that. ;-)


----- original message -----