+ Reply to Thread
Results 1 to 7 of 7

average cells with formulas in them

  1. #1
    Registered User
    Join Date
    06-20-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    11

    average cells with formulas in them

    Hi please can someone help me?

    Please view the attachment example. I am doing some Survey Analysis and have had to convert the text answers to those surveys questions into numbers.

    1= Agree
    2= Tend to agree
    3= Neutral
    4= Tend to disagree
    5= Disagree

    I now want to do an average of columns D to L in row 847 but as I used an IF formula to convert the text to numbers is won't let me average the cells as there is a formula in it.

    I also want to do a ranking in row 849 based on the average in row 847. The lower the average the higher the ranking. This way I can analyse where we need to implement an improvement process.

    Can anyone help me please?
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: average cells with formulas in them

    This is the formula that you have in D3:

    =IF(New_Hires_Survey!D3="agree","1",IF(New_Hires_Survey!D3="Tend to Agree","2",IF(New_Hires_Survey!D3="Neutral","3",IF(New_Hires_Survey!D3="Tend to Disagree","4",IF(New_Hires_Survey!D3="Disagree","5",0)))))

    The "numbers" that you are returning are actually text values because you have put quotes around them (shown in red), and this is the reason why your AVERAGE formula doesn't work. So, remove the quotes from around the numbers, then re-apply the formula.

    Incidentally, there are other ways of changing your phrases to numbers rather than using multiple-IFs - VLOOKUP would simplify your formula considerably.

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: average cells with formulas in them

    The problem is with your IF formula syntax... You have inserted the numbers as text i.e. "1" instead of 1
    =IF(New_Hires_Survey!D778="agree","1",IF(New_Hires_Survey!D778="Tend to Agree","2",IF(New_Hires_Survey!D778="Neutral","3",IF(New_Hires_Survey!D778="Tend to Disagree","4",IF(New_Hires_Survey!D778="Disagree","5",0)))))


    Example 4.xlsx
    Last edited by AndyLitch; 05-31-2013 at 06:11 AM.
    Elegant Simplicity............. Not Always

  4. #4
    Registered User
    Join Date
    06-20-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: average cells with formulas in them

    Thanks Pete that makes sense now.

    Do you know how I can enter a formula to rank my averages? I want the lowest average to rank as 1st and the highest average to rank last.

    Any help would be greatly appreciated, thank you.

  5. #5
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: average cells with formulas in them

    Answer attached in my previous post

  6. #6
    Registered User
    Join Date
    06-20-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: average cells with formulas in them

    Thats brilliant Andy thanks so much!!! Appreciate the quick response guys

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: average cells with formulas in them

    Quote Originally Posted by rjassal View Post
    Do you know how I can enter a formula to rank my averages?
    Use the RANK function.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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