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.