[mso] Re: Stump the Experts in Excel

 
  Hi Chuck,
   
  =SUMIF($B$3:$AZ$3,B3,$B$18:$AZ$18)/COUNTIF($B$3:$AZ$3,B3), If you aplly this 
formula in colum B and paste it in the same row till colum AZ u should get the 
average of what is there in Row No. 3( eg Formula in Colum B will give you 
average of John , Formula in Colum C  will give u average of Bill, etc) and not 
the averages all the numbers in row 18.
   
  Vergel suggested right, his formula will give avg of "john".
Good day!
   
  John


  Chuck,

In using the "SUMIF" and "CountIF"formula you can try putting the name "John" 
in the 
CRITERIA field.

=SUMIF($A$3:$AZ$3,"JOHN",$A$18:$AZ$18)/COUNTIF($A$3:$AZ$3,"JOHN")

Vergel
Chuck H wrote:
John,
I just got back and checked out this formula. It isn't working, but I'll bet 
you it's close! It's a much, much better idea that the lookup formulas I was 
trying. You're right in that I needed to change the formula slightly, as row 
three does start in Column B, so it now reads...($B$3:$AZ$3...). My problem is 
that I only want it to find the "John" columns and average the numbers in row 
18 only for "John." The way it is now averages all the numbers in row 18. I 
know that I have to put "John" in the formula, but so far haven't been able to 
enter it correctly, as Excel keeps telling me I'm wrong.

Chuck H.

This email and any attachments have been scanned by Norton AV Version 11.0.16.2
----- Original Message ----- 
From: joby john 
To: mso@xxxxxxxxxxxxx 
Sent: Friday, August 31, 2007 2:07 AM
Subject: [mso] Re: Stump the Experts in Excel


Hi Chuck

Sorry! some corrections to my previous post
=SUMIF($A$3:$AZ$3,A3,$A$18:$AZ$18)/COUNTIF($A$3:$AZ$3,A3)
Have assumed names in Row Three starts from Column A, 
if that is not the case modify range suitably

regards
John
joby john wrote:
Dear Chuck

Please Try this formula

=SUMIF($A$3:$AZ$3,A3,$A$18:$AZ$18)/COUNTIF($B$3:$D$3,A3)

Regards

John

Chuck H wrote:
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
BlankI've received fantastic help on formulas here before, but I think =
this one may not be able to be done. That's why I've=20
brought it to the experts to tell me if it can. Assume the following: =
the "1,2,3, etc. down the left side are the rows in an
Excel spreadsheet and the A, B, C, E are columns in the spreadsheet. =
Assume that the columns could extend on
out to "AZ." The percent in row 18 is determined by the "O" or "1" in =
the rows above it. Here is what I really need: I need
a formula that will look for each name (let's use "John" appearing in =
row 3, columns B and G. In this case, if there were=20
only the two Johns listed, then the average would obviously be 100% and =
80% divided by 2 equals 90% for the answer.=20
However, what if there were "John" in 20 of the columns. I need the =
formulas to look for all "John" appearing in row 3 and
average all of John's scores in row 18. Can that be done? Do I need a =
formula for each name (John, Bill, Susan, etc.)?
I've been looking at Lookup formulas and such, but I can't figure it =
out. I need to have final answers that say, out of all the
entries in the spreadsheet, John averaged X%, Bill averaged X%, Susan =
averaged X%, etc. That way I can compare the=20
average of each person to see who has the better averages. I hope this =
makes sense. If not, I'll try to explain better. Again,
I can't even begin to tell you how helpful some of you have been in =
helping me with some of these formulas in the past. I
sometimes think I'm at least reasonably intelligent at Excel, then =
formulas I don't understand come along to burst my
bubble. Oh, and I'm assuming you may have to adjust the size of your =
e-mail box to make this show up correctly?


A B C =
D E F G H =
=20

1 Audit of 100 People 1 2 3 4 5 6 7=20
2 Goal > or =3D 80% ID ID ID ID ID ID ID=20
3 First Name: John Bill Susan Ellen George John Susan=20
4 ID Numbers: =20
5 =20
6 Turn Around Time in Days: 5 10 6 5 6 6 5=20
7 Enter Date Received 15-Aug 15-Aug 14-Aug 15-Aug 14-Aug =
15-Aug 15-Aug=20
8 Enter Date Entered 20-Aug 25-Aug 20-Aug 20-Aug 20-Aug =
21-Aug 20-Aug=20
9 If TAT is , or =3D 5 then 1; if >5 then 0 1 0 0 =
1 0 0 1=20
10 =20
11 Category of Intervention =20
12 =20
13 Interaction Fax Back 1 1 0 0 1 0 1=20
14 Fax Back Succeeded 1 0 1 1 1 1 1=20
15 Correct MD Response Box Checked 1 1 1 1 1 0 1=20
16 All Data Entered Correctly 1 1 1 0 1 0 1=20
17 Scanned into Pt's File 1 0 1 0 1 1 0=20
18 SCORE (Enter "1" if correct or n/a; Enter "0" if missing =
or wrong 100% 80% 80% 60% 100% 80% 90%=20
19 =20
20 Auditor:

=20

Chuck H.

This email and any attachments have been scanned by Norton AV Version =
11.0.16.2




-- No attachments (even text) are allowed --
-- Type: image/gif
-- File: Blank Bkgrd.gif


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top. This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
This group will not allow for posting of emails, but will allow you to join and 
share problem files, templates, etc.: 
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************



---------------------------------
Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top. This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
This group will not allow for posting of emails, but will allow you to join and 
share problem files, templates, etc.: 
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************



---------------------------------
Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top. This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
This group will not allow for posting of emails, but will allow you to join and 
share problem files, templates, etc.: 
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top. This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
This group will not allow for posting of emails, but will allow you to join and 
share problem files, templates, etc.: 
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************



---------------------------------
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, 
when. 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top. This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
This group will not allow for posting of emails, but will allow you to join and 
share problem files, templates, etc.: 
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************


       
---------------------------------
Building a website is a piece of cake. 
Yahoo! Small Business gives you all the tools to get online.

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: