+ Reply to Thread
Results 1 to 4 of 4

Average Results from a Formula

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    Sacramento, CA
    MS-Off Ver
    2010
    Posts
    1

    Average Results from a Formula

    I have a Sales Rep Tracking sheet to track some statistics. I've created a formula to calculate a rating of 1-4 based on hitting certain numbers. Now I want to find the average of their rating but can't seem to get a formula to work on the results of rating formula. I've attached my sample. I need a formula in cell B3 to calculate the average of the results in cells C3:O3 which contain a pretty complex array formula. Can anyone tell me what to put in B3 to get the desired results?
    Thanks,
    Audrey
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average Results from a Formula

    The problem is that the formula is not returning Numbers
    =IF(C2:O2=0," ",IF(C2:O2<5,"1",IF(C2:O2<=7,"2",IF(C2:O2<=10,"3",IF(C2:O2>10,"4")))))

    "1" is actually a TEXT string, not a number.
    Same for "2" "3" and "4"

    Remove all those quote marks from the numbers.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average Results from a Formula

    Also, here's a simpler formula you can put in C2 and drag right.
    This doesn't require the CTRL + SHIFT + ENTER

    =IF(C2=0,"",MATCH(C2,{1,5,8,11}))

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Average Results from a Formula

    Not entirely sure why you have array entered the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You could just have a simple formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and drag it across.

    The problem in both cases is that a number in quotes ("") is not a number, it is text that looks like a number. You also fill with a space, not a null, if the value is zero (0)

    So, the formula should be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or, better:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you really want an Array Formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Whatever, once you have numbers, you should be able to get an average, or whatever.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to create a formula to average survey results per instructor
    By nscarritt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-28-2014, 08:55 PM
  2. [SOLVED] Average of last 7 results
    By Brian Wallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2012, 09:04 PM
  3. Get an average of top 50% of results
    By Meeker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2011, 09:56 AM
  4. Average of Results
    By cas8100 in forum Excel General
    Replies: 6
    Last Post: 11-04-2009, 12:20 PM
  5. [SOLVED] Get Average on results of Autofilter
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2006, 06:50 PM

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