[mso] Re: Stump the Experts in Excel

John and Vergel,
Thank you both very much for your assistance. My spreadsheet now does exactly 
what I need it to do. Every time I think I'm getting pretty good at Excel, 
along comes something to teach me I don't know much!  I save all the help I get 
on here, knowing I will use it someplace down the road.  Now if I could just 
figure out Access, I'd be very impressed. I've taken Saturday classes, read 
Access for Dummies - you name it and I think I've tried it - but I think my 
brain has a short circuit when it comes to Access. At least, I'm improving at 
Excel daily thanks to the great people like yourselves and others on this list 
who have provided some really good assistance.

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: Monday, September 03, 2007 1:47 PM
  Subject: [mso] Re: Stump the Experts in Excel


  Chuck
     
    Correct .. Vergel"s formula will give the result.
    Sorry ! from your original post I had an impression that ur requirement is 
to get average of all the persons in Row 3( john,bill etc). 
    Now I understand u need average of only for "john"!
     
    I am not sure if u have noticed that in case u are putting the formula in 
each colum the result it derives will be the same of every john , and evrey 
bill etc. in other words in Row 3 where ever "john" appears ,corresponding 
formula in the colum will give only one result! Only one result , though it may 
be repeating ! so u need not further put formula to average it!
    Hope I am not making things coplicated for you!
     
    Regards
     
    John
     
     
     
    
  Chuck H <chawsey@xxxxxxxxxxx> wrote:
    If "John" appears in several of the rows, can one formula simply give me 
the average of all rows in which John appears. I suppose I could put the 
formula below in each column, but that would then average John (along with 
Bill, etc.) in each row, but then I will need to put in another formula to 
average all the rows with John in them (and Bill, etc.). Correct, or am I 
missing something. I would appear to me that by using Vergel's formula, It 
would average all the rows in which the name was John with the one formula, 
correct?
  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: Monday, September 03, 2007 12:27 AM
  Subject: [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
  *************************************************************

  *************************************************************
  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
  *************************************************************


         
  ---------------------------------
  Fussy? Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user panel 
and lay it on us.

  *************************************************************
  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
*************************************************************

Other related posts: