+ Reply to Thread
Results 1 to 5 of 5

Percentage Formula for rooms completed

  1. 07-19-2013, 06:31 PM #1
    tf4string
    tf4string is offline
    Registered User
    Join Date
    07-19-2013
    Location
    New York, USA
    MS-Off Ver
    Office 2010
    Posts
    2

    Percentage Formula for rooms completed

    Hello,
    I was hoping to get a little help with an Excel formula that I am trying to use. I have a chart of rooms that need work to be done in each. So I have my Rows with each floor (room numbers are the same on each floor). I configure the font as Wingdings so that I can have checkboxes. See the screen shot for an idea of what I have.

    Ideally I want to have a percentage list on another sheet that looks at the cells as either "q" or "x", giving it 0 for q and 1 for x. I have tried a COUNTA/COLUMNS formula that I found in another post but it didn't really work. It gives 100% no matter if I have text or not.

    rooms-excel.JPG

    Any ideas?
    Many thanks,
    Tim
    Reply With QuoteRegister To Reply
  2. 07-19-2013, 06:37 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Percentage Formula for rooms completed

    post a real workbook not an image..but count(if) would be more suitable countif(a2:x2="Q")/count(a2:x2)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 07-19-2013, 06:45 PM #3
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Percentage Formula for rooms completed

    try loading the actual spreadsheet maybe easier

    COUNTIF( range, "x")
    COUNTIF( range, "q")

    I used the Marlett font and had a dropdown that allowed blank or an a to get a tick
    and the counted those a for all sorts of calculations in a school register
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 07-19-2013, 07:23 PM #4
    tf4string
    tf4string is offline
    Registered User
    Join Date
    07-19-2013
    Location
    New York, USA
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Percentage Formula for rooms completed

    Hello,
    Thank you both for your feedback. I've tried variations of countif and count get keep getting too few arguments error. As suggested I have attached a sample of my spreadsheet.

    Book1.xlsx

    Also the working formula I have so far is:
    =COUNTA(ProjectStatus!B4:Q4/ROWS(ProjectStatus!B4:Q4))

    Many thanks,
    Tim
    Reply With QuoteRegister To Reply
  5. 07-19-2013, 07:38 PM #5
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Percentage Formula for rooms completed

    can you describe what you would like a percent of

    rows( b4:q4) will just return 1

    and theres no sheet called project status

    so do you want to see the number of crosses as a percent per row

    =COUNTIF(B4:Q4,"x")/ ( =COUNTIF(B4:Q4,"x")+=COUNTIF(B4:Q4,"q")) or 16
    would give the Xs

    OR

    =COUNTIF(B4:Q4,"x")/COLUMNS(B4:Q4)

    OR - depending on which percent you are after
    =COUNTIF(B4:Q4,"q")/COLUMNS(B4:Q4)
    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. 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
  2. Percentage Completed Reference...
    By DMA-Pacific in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-21-2011, 04:27 AM
  3. Percentage Completed with Checkboxes....
    By DMA-Pacific in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2011, 10:41 AM
  4. Percentage of entries completed
    By lfb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2011, 04:34 AM
  5. Calculating the Percentage Completed
    By KyrithAZ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2006, 03:40 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1