We help IT Professionals succeed at work.
Get Started

formula that returns a value referring to a sorted list if less than criteria, but will not return 0 if first value is greater than criteria

Berry Metzger
Last Modified: 2012-05-12
Need #1: A common [copyable] formula in col E to return "80%" if result is <=0.8 AND, if first formula in each set happens to exceed 0.8 of that data set as the case at cell E12, then that formula also returns "80%"  (0.8) such that the count formulae in Col F return at least 1, never 0 as when the first value of each dataset in col D happens to be >0.8... all formulae shall be the same to enable copying to other cells!

Need #2: a Conditional Formatting method to hide formulae [in Col E] that return #N/A.   Workaround: hide #N/A cells manually using white font but this is a kludge requiring adjusting the font color.  Using "" in formulae cause charted series to descend to 0 (unwanted) but formulas hide nicely (wanted).  Looking to hide formulas that return #N/A but do not descend to zero on charts.  

Please see attached worksheet with explanations and desired outcomes.

Thanks in advance.
Berry LessThan80Pct-1MoreIf1st-1Too.xls
Watch Question
Most Valuable Expert 2013
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE