| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating %'s with IF and Sum/SumIF
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
That formula does not work correctly, it errors out on input, 'too few
arguments'. What is the real formula? -- 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
"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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
Your formula refers to data in row 7, but your example shows nothing, and
what about GradeTbl? -- 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
"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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
"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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
Thank you both,Bob & Joe. The solution works great.
|
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF function not calculating from closed worksheet | Excel Worksheet Functions | |||
| Problem calculating with a SUMIF | Excel Worksheet Functions | |||
| SUMIF Not Calculating | Excel Worksheet Functions | |||
| SUMIF Not Calculating | Excel Worksheet Functions | |||
| SUMIF not calculating | Excel Worksheet Functions | |||
Linear Mode
