• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with a formula to return all if one is 95% complete

R

Rhonda

New Member
I have a list of contract numbers (J2:J732), some of which are duplicated because the contract contains multiple projects. In another column I have a list of percentages (O2:O732). These percentages reflect the amount of work that has been completed.

I need to show projects that are 95% or more completed but I also need to include any projects that have the same contract number. So if there are 5 projects in a contract and 1 or more of them are 95% or more complete, I need to show all 5. This could be shown as true/false in column Q.

I really appreciate any help you might offer. I've been trying to come up with a solution and I'm simply lost.
Thanks,
Rhonda
 
If all records show as FALSE then no project is more than 95% complete, even if you haven't array-entered the formula.
95% = 0.95
What range of values are there in column O? 1 to 100?, 0.01 to 1.00?
(To find out, bring up the Format cells dialogue box, in the Number tab, select General and it will show you the value in the Sample box. Escape out of this dialogue to prevent making any changes.

It really is impossible to try and guess what you have on your spreadsheet, so supply a file to work on.
 
I've been playing with index and match and commented out my attempt (which also does not work).

As you can see the values in column O range from zero to over 100%.

Thank you for your help.
 

Attachments

  • TestProjectStatusOver95Percent.xlsx
    251.3 KB · Views: 1
I do not understand why this is not working for me. I copied and pasted your formula and used Ctrl+Shift+Enter and copied down.

I am curious what that answer is on row 28. This (I believe) is the first instance of a multi-project contract that have some over 95% and some under 95%.

I'll keep fiddling with this - it does not make sense to me that it works for you and not me.
 
This is just weird! I closed and saved the file so I could send it and then when I opened it back up all the answers were correct. Dang I'm glad it's Friday!
 
Thank you so much for your help. I'm really not as crazy as I seem. There have been some strange things happening with Excel since my agency moved to O365. I wonder if I accidently changed that and didn't realize it. You are a lifesaver!!! Thank you, thank you, thank you!!! :):):)
 
You must log in or register to reply here.
Back
Top