+ Reply to Thread
Results 1 to 2 of 2

Trying to get percentage of results

  1. #1
    Registered User
    Join Date
    10-07-2016
    Location
    Lincolnshire
    MS-Off Ver
    2010
    Posts
    15

    Trying to get percentage of results

    Hi all,

    I have a sheet which has 5 values in cells C32, F32, I32, L32 and O32. The number is always between 0 and 35. I want a formula to look at those 5 cells and tell me what percentage of the results are between 0 - 15, 16 - 29 and 30+. I've tried lots of variations but getting nowhere fast. A25 is the first cell data would be entered into so if blank, I want no error.

    The formula I'm at so far is shown below:

    =IF(ISERROR(IF(ISBLANK(A25),"0 - 15 mins = 0% 16 - 29 mins = 0% 30+ mins = 0%",CONCATENATE(" 0 - 15 mins = ",TEXT(COUNTIF(C32+F32+I32+L32+O32,1),"0%")," 16 - 29 mins = ",TEXT(COUNTIF(C32+F32+I32+L32+O32,2),"0%")," "," 30+ mins = ",TEXT(COUNTIF(C32+F32+I32+L32+O32,3),"0%")))

    Anyone any ideas?

    TIA

    Jack

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Trying to get percentage of results

    Your formula does not do a division anywhere to determine a percentage, so I don't know how you were expecting your formula to yield a percentage. You are not using COUNTIF correctly; I'm not sure what you intend with 1, 2, and 3 as the second argument.

    The only interpretation I have of your description is what is the percentage out of those 5 values. So the percentage for any given range can only be 0%, 20%, 40%, 60%, 80%, or 100%. If that is not correct then you need to explain what you mean by a "percentage of the results."

    You are doing three things in your sheet design that are making your life harder:

    1. You are doing analysis on a discontiguous range. It is difficult to use COUNTIFS (you need that along with COUNTIF) with a discontiguous range, although it's possible.
    2. You are generating your result in a single cell. It would easier and probably look better to separate your results into three cells. This would also eliminate the need to use TEXT for formatting.
    3. You are baking constants into your formula. It would be better to make a table that shows the minimum and maximum values of each range.

    If you were to do those three things you would have a very simple formula. Instead you have this:

    ="0-15: "&TEXT(SUM(COUNTIF(INDIRECT({"C32","F32","I32","L32","O32"}),"<=15"))/SUM(COUNTA(INDIRECT({"C32","F32","I32","L32","O32"}))),"0%")&" 16-29 : "&TEXT(SUM(COUNTIFS(INDIRECT({"C32","F32","I32","L32","O32"}),">=16",INDIRECT({"C32","F32","I32","L32","O32"}),"<=29"))/SUM(COUNTA(INDIRECT({"C32","F32","I32","L32","O32"}))),"0%")&" 30+: "&TEXT(SUM(COUNTIF(INDIRECT({"C32","F32","I32","L32","O32"}),">=30"))/SUM(COUNTA(INDIRECT({"C32","F32","I32","L32","O32"}))),"0%")

    You'll have to add a check for A25, which will make it even more complicated.
    Last edited by 6StringJazzer; 09-23-2018 at 08:21 PM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. [SOLVED] Weekly bar chart needed for percentage results
    By ~TaC~ in forum Excel General
    Replies: 3
    Last Post: 05-04-2018, 01:14 PM
  2. [SOLVED] Percentage results of zero comparing 2 years
    By MariaPap in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2017, 11:52 AM
  3. Doughnut Chart - Results V.s Target and showing percentage
    By nelson2101 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-23-2017, 12:17 PM
  4. [SOLVED] Show percentage of results in a Pivot table
    By rogelinepaula in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 11-24-2015, 09:44 PM
  5. Replies: 5
    Last Post: 03-18-2015, 08:23 PM
  6. Multiply by percentage and round final results
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-04-2012, 05:51 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