+ Reply to Thread
Results 1 to 12 of 12

Finding an Average

  1. 07-09-2008, 01:07 PM #1
    dmay1102
    dmay1102 is offline
    Registered User
    Join Date
    07-09-2008
    Location
    DC
    Posts
    18

    Finding an Average

    Hey everyone,

    I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.

    I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.

    Thanks a lot.
    Reply With QuoteRegister To Reply
  2. 07-09-2008, 01:25 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Try:

    =AVERAGE(LEFT(A1,FIND("-",A1)-1),MID(A1,FIND("-",A1)+1,255))

    where A1 houses the irange
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 07-09-2008, 01:30 PM #3
    dmay1102
    dmay1102 is offline
    Registered User
    Join Date
    07-09-2008
    Location
    DC
    Posts
    18
    Wow that's great. Thanks so much
    Reply With QuoteRegister To Reply
  4. 07-09-2008, 01:35 PM #4
    dmay1102
    dmay1102 is offline
    Registered User
    Join Date
    07-09-2008
    Location
    DC
    Posts
    18
    Not to push my luck, but is there a way to modify it slightly so that if I don't have a range (it is just one number) then it will just take that number. Thanks a bunch
    Reply With QuoteRegister To Reply
  5. 07-09-2008, 01:35 PM #5
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    =left(a1,find("-",a1) -1)+mid(a1,find("-",a1)+1,50)/2
    Reply With QuoteRegister To Reply
  6. 07-09-2008, 01:41 PM #6
    dmay1102
    dmay1102 is offline
    Registered User
    Join Date
    07-09-2008
    Location
    DC
    Posts
    18
    Not working sorry. That adds the minimum to half of the maximum.
    Reply With QuoteRegister To Reply
  7. 07-09-2008, 01:44 PM #7
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    Please Login or Register  to view this content.
    other should have been
    =(LEFT(A1,FIND("-",A1) -1)+MID(A1,FIND("-",A1)+1,255))/2
    i missed a pair of brackets !!!!
    Last edited by martindwilson; 07-09-2008 at 01:51 PM.
    Reply With QuoteRegister To Reply
  8. 07-09-2008, 02:03 PM #8
    dmay1102
    dmay1102 is offline
    Registered User
    Join Date
    07-09-2008
    Location
    DC
    Posts
    18
    Wonderful! Last thing, is there a way to not get the "div/o" text if the two cells it is pulling numbers from are blank?
    Reply With QuoteRegister To Reply
  9. 07-09-2008, 02:09 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by dmay1102
    Wonderful! Last thing, is there a way to not get the "div/o" text if the two cells it is pulling numbers from are blank?
    Try for all conditions mentioned...

    =IF(ISNUMBER(A1),A1,IF(A1="","",AVERAGE(LEFT(A1,FIND("-",A1)-1),MID(A1,FIND("-",A1)+1,255))))
    Reply With QuoteRegister To Reply
  10. 07-09-2008, 02:17 PM #10
    dmay1102
    dmay1102 is offline
    Registered User
    Join Date
    07-09-2008
    Location
    DC
    Posts
    18
    I should have mentioned that I am dividing the average of one cell by that of another.

    Please Login or Register  to view this content.
    That is the code to divide the average of B6 by B7. The formula gave me works (and is blank if there is nothing in the reference cell), but when i try to do this extended formula I get the "#Value!" message. Can i also get this cell to be blank?

    Thanks.
    Last edited by NBVC; 07-09-2008 at 02:25 PM.
    Reply With QuoteRegister To Reply
  11. 07-09-2008, 02:25 PM #11
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Try:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 07-09-2008, 02:36 PM #12
    dmay1102
    dmay1102 is offline
    Registered User
    Join Date
    07-09-2008
    Location
    DC
    Posts
    18
    thanks you guys are the best
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1