+ Reply to Thread
Results 1 to 7 of 7

Mean, median and mode

  1. #1
    Registered User
    Join Date
    10-23-2007
    Posts
    19

    Mean, median and mode

    Is there a good/easy formula to use when trying to find the mean, median and mode for a column? If so, can someone post it? I'd like to make it as easy as possible (obviously!).

    Thanks

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Brandon,

    Excel contains the following formulas to do this. This example is for column "A".
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    mean is average mean from statistical functions so for column c
    =AVERAGE(C:C)
    oh there was i looking them up and lr beat me to it again

  4. #4
    Registered User
    Join Date
    10-23-2007
    Posts
    19
    Works like a charm! Thanks a million!

  5. #5
    Registered User
    Join Date
    10-23-2007
    Posts
    19
    OK, so it worked REALLY well but of course a twist is now thrown into the mix.

    Suppose my numbers are in column L. If the report is marked as unsigned it will have a U in column K and L will read as -38,000 or other very high number. This number skews my data.

    Is there a if/then statement I can add to the =AVERAGE to say if column K = U then do not add this data into the average?

    Thanks

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You can use an "array formula" like this

    =IF(K2:K100<>"U",IF(L2:L100,L2:L100)))

    needs to be confirmed with CTRL+SHIFT+ENTER

  7. #7
    Registered User
    Join Date
    10-23-2007
    Posts
    19
    That doesn't seem to be doing the trick. In line 46808 I put this in:

    =IF(M2:M46807<>"U",IF(N2:N46807,N2:N46807))

    N is the column with the number of days. Column M has the status (signed, unsigned, incomplete). I just want the signed documents to be averaged. I guess I could sort them by letter and just do the math but automation would be a lot more cool

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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