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

Berry Metzger
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.

Thanks in advance.
Berry LessThan80Pct-1MoreIf1st-1Too.xls
Comment
Watch Question

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

Open in new window


Need #2
=ISERROR(E1)  Applies to $E:$E

Open in new window

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
Berry MetzgerLean process improvement consultant

Author

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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.