If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Calculating %'s with IF and Sum/SumIF
"Bob Phillips" wrote:
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(G8:P8,U8:V8) or SUM(U8,V8) to be consistently extensible. ----- original message ----- "Bob Phillips" wrote in 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. -- HTH Bob "Joe User" joeu2004 wrote in message ... "Bob Phillips" wrote: =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)) 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 ----- "Bob Phillips" wrote in message ... 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 "Billy B" wrote in message ... "Bob Phillips" wrote: 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 "Billy B" wrote in message ... 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. 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 |
#12
|
|||
|
|||
Calculating %'s with IF and Sum/SumIF
Yeah, looking at it now, but when I built my answer that was the part I
wasn't looking at :-( -- HTH Bob "Joe User" joeu2004 wrote in message ... "Bob Phillips" wrote: 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(G8:P8,U8:V8) or SUM(U8,V8) to be consistently extensible. ----- original message ----- "Bob Phillips" wrote in 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. -- HTH Bob "Joe User" joeu2004 wrote in message ... "Bob Phillips" wrote: =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)) 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 ----- "Bob Phillips" wrote in message ... 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 "Billy B" wrote in message ... "Bob Phillips" wrote: 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 "Billy B" wrote in message ... 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. 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 |
#13
|
|||
|
|||
Calculating %'s with IF and Sum/SumIF
"Bob Phillips" wrote:
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 ----- "Bob Phillips" wrote in message ... Yeah, looking at it now, but when I built my answer that was the part I wasn't looking at :-( -- HTH Bob "Joe User" joeu2004 wrote in message ... "Bob Phillips" wrote: 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(G8:P8,U8:V8) or SUM(U8,V8) to be consistently extensible. ----- original message ----- "Bob Phillips" wrote in 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. -- HTH Bob "Joe User" joeu2004 wrote in message ... "Bob Phillips" wrote: =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)) 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 ----- "Bob Phillips" wrote in message ... 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 "Billy B" wrote in message ... "Bob Phillips" wrote: 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 "Billy B" wrote in message ... 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. 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 |
|
Thread Tools | |
Display Modes | |
|
|