+ Reply to Thread
Results 1 to 4 of 4

How to calculate % Completed over a range of cells

  1. 08-07-2018, 06:30 AM #1
    Mike260460
    Mike260460 is offline
    Registered User
    Join Date
    11-05-2015
    Location
    Lincoln , England
    MS-Off Ver
    MS office Excel Mac 2011
    Posts
    5

    How to calculate % Completed over a range of cells

    Hello, i have a small degree of excel experience but trying to calculate a % over a range of cells, removing the formula or calculation when cells that show N/A are not counted in the total number or range.

    I have attached a small worksheet,i hope for all to see- where you will see the range of cells; B5:E9
    from the 20 cells within the range - 4 = N/A

    What is the formula to calculate the % of the range to include the range of N/A cells but not include in the % overall, that shows Completed as that is number i would like.

    I hope you understand my text... hoping the forum can help me.

    Thank you Michael
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-07-2018, 06:39 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,413

    Re: How to calculate % Completed over a range of cells

    So... do you want the no of "completed", expressed as a % of the number of cells that are not N/A???

    =COUNTIF($B$5:$E$9,"Completed")/(20-COUNTIF($B$5:$E$9,"N/A"))

    formatted as % will do that.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 08-07-2018, 09:34 AM #3
    Mike260460
    Mike260460 is offline
    Registered User
    Join Date
    11-05-2015
    Location
    Lincoln , England
    MS-Off Ver
    MS office Excel Mac 2011
    Posts
    5

    Re: How to calculate % Completed over a range of cells

    Thank you Glen for the reply to my problem, pleased to say that formula is perfect thank you Mike
    Reply With QuoteRegister To Reply
  4. 08-07-2018, 09:36 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,413

    Re: How to calculate % Completed over a range of cells

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    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] Calculate average value of multiple cells only if cell is positive? Cells not in a range.
    By Elijah in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2018, 12:48 AM
  2. Calculate On Time or Late when Completed date is blank
    By dlayton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-16-2018, 06:12 PM
  3. [SOLVED] Formula To Calculate Hours Completed Month To Date
    By rahul_ferns76 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-31-2016, 10:46 AM
  4. Automatically copying the range of cells under the lastest completed cell from a row
    By Niels57440 in forum Excel General
    Replies: 2
    Last Post: 02-15-2016, 12:41 PM
  5. Calculate days between two dates, until job completed.
    By StonyMac in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-06-2013, 04:32 AM
  6. Formula to calculate the percentage of work completed
    By Zodiark in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2012, 04:42 PM
  7. Calculate % of tasks completed
    By sanlen in forum Excel General
    Replies: 2
    Last Post: 08-30-2010, 08:43 AM

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