+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : How to create an 'Average' formula with numbers and letters Excel 2011 Mac

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    Chile
    MS-Off Ver
    Excel 2011 for MAC
    Posts
    8

    Exclamation How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    Hello to All!

    I'm in need of solving this problem in a quick fashion. I'm not new to excel but I am very new to creating formulas. I need to create a formula for the 'average' attendance of my students at my company. I'm the coordinator at a large company for an English school we've installed and I have over 90 students at the time (which will grow and grow) and I am in charge of the attendance. I need to add value to the pound sign/letters and create a formula for the average.

    I originally had the attendance as:
    1=Attended
    0=Did not attend and it's approved
    #=Did not attend and it is not approved
    ###=Did not attend, to be determined if approved or not.

    #=0
    ###=0

    But then I was told that it would be easier to change the pound sign to a letter for formula changes, which for input purposes I prefer continuing to use the pound sign but you can't get everything you want

    Now the attendance will be changed to:

    1=Attended
    0=Did not attend and it's approved
    A=Did not attend and it is not approved
    NA=Did not attend, to be determined if approved or not.

    A=0
    NA=0

    Average Attendance
    60% 1 1 A NA 1
    40% 0 1 0 1 A
    60% 1 # 1 1 ###
    40% 0 1 # ### 1

    Last note: I would like for all percentages 74% and below to be in RED.

    Thank you very much in advance!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    try something like this
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-06-2012
    Location
    Chile
    MS-Off Ver
    Excel 2011 for MAC
    Posts
    8

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    Almost worked perfectly except the number of classes they attend will change on a weekly basis. By the end of the program the students will have attended 61 classes.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    assuming no gaps in the row use something like =countif(b1/b100,1)/counta(b1:b100) i cant guess at your layout,
    Last edited by martindwilson; 06-07-2012 at 05:07 AM.

  5. #5
    Registered User
    Join Date
    06-06-2012
    Location
    Chile
    MS-Off Ver
    Excel 2011 for MAC
    Posts
    8

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    How do I attach a file to this site? This way I am able to upload my spreadsheet.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    To attach a file to a new post, simply click the [Browse] button at the bottom of the post composition page, and locate the file that you want to attach from your local hard drive.

    Only certain types of files may be attached: these are the valid file extensions for files to be attached to this forum: .

    After posting, the attachment will show up in the body of your message. To view the contents of the attachment (if it is not already displayed) simply click the filename link that appears next to the attachment icon .

  7. #7
    Registered User
    Join Date
    06-06-2012
    Location
    Chile
    MS-Off Ver
    Excel 2011 for MAC
    Posts
    8

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    Here is one of my 27 groups as an example of my spreadsheet
    Attached Files Attached Files

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    ok here you go this will cater for 248 entries along the row(thats coz im using '97 and only have 256 columns)
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-06-2012
    Location
    Chile
    MS-Off Ver
    Excel 2011 for MAC
    Posts
    8

    Re: How to create an 'Average' formula with numbers and letters Excel 2011 Mac

    Thank you so much everyone! My problem has been solved and I now understand Excel a little bit better!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1