We help IT Professionals succeed at work.

# 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

on
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.

Berry LessThan80Pct-1MoreIf1st-1Too.xls
Comment
Watch Question

## View Solutions Only

Commented:
Need #1
``````=MAX(COUNT(E2:E9),1)
``````

Need #2
``````=ISERROR(E1)  Applies to \$E:\$E
``````
LessThan80Pct-1MoreIf1st-1Too---.xls
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Hello Berry, I think you can use this formula for E12 copied down

=IF(SUM(D\$12:D12)<=80%,0.8,IF(COUNT(D\$12:D12)=1,0.8,NA()))

change cell reference for other ranges. F2/F12/F22 formulas can remain unchanged. Using this approach keeps your chart titles without error as you suggested

I used =ISNA(E1) for the conditional formatting but ISERROR as suggested by Amick will also work

see attached

regards, barry
barry-CF.xls
Lean process improvement consultant

Commented:
Thanks to you both, Amick & Barry
Because Barry's solution is complete and accurate, I awarded it the most points because it solves the both requirements to display at least one value and chart properly while still enabling hiding the values using CF.