Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2010
    Location
    Detroit, Michigan
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    =('belt 4'!C3+'Belt 3'!C4+'Belt 3'!C3+'Belt 3'!C12+'Belt 3'!C13)/5

    These are being pulled from 2 different sheets, added then divided to get the average. problem is that sometimes the data isn't a number (99.5, 99.3, 97.9, OFF, 84.5) - So I want to exclude the OFF or convert it into a 0 with the formula.

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Try using the AVERAGE function - it will disregard text
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    New Lounger
    Join Date
    May 2010
    Location
    Detroit, Michigan
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Catharine Richardson - WebGenii View Post
    Try using the AVERAGE function - it will disregard text
    No it still returns the #VALUE error

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Ignoring or converting to zero will give entirely different answers. Ignoring will divide by the sum by 4 convering to zero will divide by 5.

    Which answer do you want?

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Did you change the formula to:

    Code:
    =AVERAGE('Belt 4'!C3,'belt 3'!C4,'belt 3'!C3,'belt 3'!C12,'belt 3'!C13)
    I'm guessing you have
    Code:
    =AVERAGE('Belt 4'!C3+'belt 3'!C4+'belt 3'!C3+'belt 3'!C12+'belt 3'!C13)

  6. #6
    New Lounger
    Join Date
    May 2010
    Location
    Detroit, Michigan
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Ignoring or converting to zero will give entirely different answers. Ignoring will divide by the sum by 4 convering to zero will divide by 5.

    Which answer do you want?

    Steve
    I want it to ignore the text and only factor in the numbers and divide the sum in my example by 4 if one is a text value.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Excel Paul View Post
    I want it to ignore the text and only factor in the numbers and divide the sum in my example by 4 if one is a text value.
    Then using:
    Code:
    =AVERAGE('Belt 4'!C3,'belt 3'!C4,'belt 3'!C3,'belt 3'!C12,'belt 3'!C13)
    will work.

Posting Permissions

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