+ Reply to Thread
Results 1 to 5 of 5

Finding Percentage of Awarded Projects from a changing list of Projects

  1. #1
    Registered User
    Join Date
    07-25-2017
    Location
    St. Paul, Minnesota
    MS-Off Ver
    2013
    Posts
    10

    Finding Percentage of Awarded Projects from a changing list of Projects

    Hi, this is my first post so bear with me please!

    I am working on building an Excel Sheet that has a list of projects I have bid on. This list is being added to all the time. The most recent projects get added to the bottom of the list. I want to keep a running percentage of the number of projects awarded divided by the number of projects bid on:

    # projects awarded / # projects bid

    The tricky part is combing functions in the cell where the percentage is displayed (at the top of the page) because the number of projects will change as I add to the list. So I cant just divide by a constant number. I am looking for a function that will find the number of cells in a Column that have data in them, and divide by a different number.

    What I was thinking was having a column (titled "Awarded ??") that will be populated with "yes" if the project was awarded, and left blank if the project was not given to me. Then I will use a COUNT function to find the number of "yes". Then I just need to find the total number of projects ( at that point in time) and divide the two.

    I have tried using two count functions (counting names of projects, and counting job numbers if awarded) and dividing them but was given an error message of "#DIV/0!"

    Any suggestions would be a huge help, Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Finding Percentage of Awarded Projects from a changing list of Projects

    I think you should investigate the COUNTIF and COUNTIFS functions. Also, possibly the COUNTA function.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Finding Percentage of Awarded Projects from a changing list of Projects

    Hello swvogt and Welcome to Excel Forum.
    Since the new projects are added to the bottom and since you would like to have the formula update as new projects are added I would suggest using an Excel Table. When you are ready to add a new project go to the furthest cell to the right in the last row (B7 in the attached file) and press the tab key.
    When a new project name is typed in the following formula will automatically update:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: the counta function counts any cell containing text, however since you said that projects not awarded will be left blank it works, if you decide later to input some text in those cases where projects are not awarded, you will probably want to use countifs.
    If the attached file doesn't work for you then please attach a sample workbook showing the type layout and expected result (manually calculated) you want.
    To attach a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    07-25-2017
    Location
    St. Paul, Minnesota
    MS-Off Ver
    2013
    Posts
    10

    Re: Finding Percentage of Awarded Projects from a changing list of Projects

    Hi guys, thanks for the responses! I tried the CountA function using tables, and that worked well. I also solved it a different (more complicated) way using IF and OR functions. Attached is the sample file with Sample numbers. The Table function is used near the top labeled "# Bids Awarded / # Bids". The complicated functions are used in column Q and do essentially the same thing.

    Thanks again for the help!
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Finding Percentage of Awarded Projects from a changing list of Projects

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ 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. Making a list with projects depending on end year
    By gandreso in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2016, 10:21 AM
  2. [SOLVED] Need sum function if project name is found in cell with alt enter used to list projects
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2015, 08:58 PM
  3. [SOLVED] Need count formula based on list of projects all in one cell
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2015, 08:21 PM
  4. Any template to track multiple projects with changing deadlines?
    By honeydolist in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2015, 06:13 AM
  5. How to work out percentage of projects delivered on time
    By Vinnyvagus in forum Excel General
    Replies: 5
    Last Post: 02-14-2014, 04:24 PM
  6. Display Projects from a list using a Macro.
    By Irish RayRay in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2012, 09:44 AM
  7. Generate List of Inactive Projects Using Two Lists
    By skoodog in forum Excel General
    Replies: 3
    Last Post: 03-27-2009, 06:11 PM

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