+ Reply to Thread
Results 1 to 8 of 8

Formula for average %

  1. #1
    Registered User
    Join Date
    12-23-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    Formula for average %

    Hi,

    I think it will be self-explanatory from the formula what I'm trying to do, but I've gone wrong somewhere....
    =IF((R20:R393>0.0%),AVERAGE(R20:R393))

    Can anyone offer a fix please?

    Thanks,
    Jon

  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,604

    Re: Formula for average %

    It is clear that you are trying to look at the range R20:R393 in the first part of your formula, but how can a range (by itself) be greater than a percentage? Do you mean that at least one cell in that range should be more than 0.0%, or that all cells are greater than zero?

    Pete

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for average %

    Did you mean ...

    =AVERAGEIF(R20:R393, ">0", R20:R393)
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    12-23-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    Re: Formula for average %

    Perfect, thanks for your help.

  5. #5
    Registered User
    Join Date
    12-23-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    Re: Formula for average %

    I was trying to get the average of all cells within the range where the content was greater than 0.0%. Now solved.

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

    Re: Formula for average %

    Well, I'm glad that shg understood your meaning, and that you got your answer, but it was not "self-explanatory" to me.

    Pete

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for average %

    Creestal bawl berrry clear twoday.

  8. #8
    Registered User
    Join Date
    12-23-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    Re: Formula for average %

    Quote Originally Posted by Pete_UK View Post
    Well, I'm glad that shg understood your meaning, and that you got your answer, but it was not "self-explanatory" to me.

    Pete
    Fair enough. My apologies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 09-11-2018, 10:29 AM
  2. Replies: 2
    Last Post: 04-17-2018, 09:45 PM
  3. [SOLVED] Using the Average formula in VBA gives different results to worksheet Average formula
    By Sc0ut in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-06-2016, 06:02 AM
  4. [SOLVED] Average formula that shows 0 but does not count in average
    By Lewster in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-11-2015, 04:49 PM
  5. [SOLVED] Rank numbers by average with doing average formula
    By makinmomb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-26-2014, 11:58 PM
  6. [SOLVED] need formula to average cells but if one cell has N/A then dont average and input N/A
    By CityInspector in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 04:16 PM
  7. Replies: 0
    Last Post: 01-22-2013, 12:22 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1