[mso] Re: Stump the Experts in Excel
- From: joby john <john_joby@xxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Sun, 2 Sep 2007 22:27:30 -0700 (PDT)
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).
//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).
//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).
//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).
//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).
//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).
//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:
