+ Reply to Thread
Results 1 to 6 of 6

Average calculation of numbers with special characters

  1. 10-06-2013, 06:19 PM #1
    Ash248
    Ash248 is offline
    Registered User
    Join Date
    01-30-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    13

    Average calculation of numbers with special characters

    Hi All...

    Wonder if someone could help me with this excel formula. I am trying to calculate the average for the values in two cells with numbers and special characters. The formula i am using is.

    =SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(D6:E9,"▼",""),"▲","")*1))/COUNTA(D6:E9)

    it is coming up with #VALUE ERROR. And i can't get my head around. May be i am going through the thinking cramp in my head. Your help is much appreciated. I have also attached a sample file for you guys to have a look. Thanks in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-06-2013, 06:31 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Average calculation of numbers with special characters

    This formula will not work on your spreadsheet. You have to remove merged cells and cells with data should be one under the other without empty rows in between.
    Last edited by AlKey; 10-06-2013 at 06:34 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 10-06-2013, 06:42 PM #3
    Ash248
    Ash248 is offline
    Registered User
    Join Date
    01-30-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Average calculation of numbers with special characters

    Thanks AlKey for pointing out the mistake i was making...much appreciated.
    Reply With QuoteRegister To Reply
  4. 10-06-2013, 06:44 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: Average calculation of numbers with special characters

    Why the need to merge cells? merged cells cause more problems than anything!!

    If you put those 2 "values" in F6:F7, ,then use that same formula, you get an answer of 8.625
    F6=10.00 ▲
    F7=7.25 ▼
    F8=8.625
    F8=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(F6:F7,"▼",""),"▲","")*1))/COUNTA(F6:F7)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 10-06-2013, 06:46 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Average calculation of numbers with special characters

    You can also use this formula for Average

    =AVERAGE(VALUE(LEFT(F6:F7,LEN(F6:F7)-2)))

    This must entered as an array formula with Ctrl+Shift+Enter

    F
    G
    5
    6
    10.00 ▲
    		 =AVERAGE(VALUE(LEFT(F6:F7,LEN(F6:F7)-2)))
    7
    7.25 ▼
    8
    8.63
    <---- in F8
    9
    Reply With QuoteRegister To Reply
  6. 10-06-2013, 06:47 PM #6
    tony h
    tony h is offline
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Average calculation of numbers with special characters

    The others make practical comments but if you need to keep the blank rows and cannot split the number then you will need a formula of the following sort
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    click on the * Add Reputation if this was useful or entertaining.
    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)

Similar Threads

  1. [SOLVED] Calculating the average for numbers with special characters.
    By Ash248 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-06-2013, 01:10 PM
  2. [SOLVED] i want re alter my numbers with special characters
    By vengatvj in forum Excel General
    Replies: 20
    Last Post: 09-30-2013, 06:51 PM
  3. How to calculate only values with numbers not special characters like!
    By toplisek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-04-2013, 06:43 AM
  4. [SOLVED] remove spaces (special characters) after numbers
    By fareastwarriors in forum Excel General
    Replies: 13
    Last Post: 11-04-2012, 09:25 PM
  5. Disallow Special Characters,Certain Numbers Using Data Validation through VBA
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-21-2011, 03: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1