|
| 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 |
|
#1
March 14th, 2010, 05:11 PM
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
March 14th, 2010, 05:17 PM
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
March 14th, 2010, 05:20 PM
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
March 14th, 2010, 08:50 PM
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
March 14th, 2010, 10:36 PM
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
March 14th, 2010, 10:44 PM
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
March 14th, 2010, 11:33 PM
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
March 14th, 2010, 11:49 PM
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
March 15th, 2010, 02:35 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating %'s with IF and Sum/SumIF
Thank you both,Bob & Joe. The solution works great.
|
|
#10
March 15th, 2010, 08:41 AM
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
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
