A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating %'s with IF and Sum/SumIF



 
 
Thread Tools Display Modes
  #11  
Old March 15th, 2010, 05:12 PM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default 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  
Old March 15th, 2010, 10:12 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default 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  
Old March 16th, 2010, 01:18 AM posted to microsoft.public.excel.worksheet.functions
Joe User[_2_]
external usenet poster
 
Posts: 757
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.