Counting the number of cells with a certain numerical pattern

Question

The following is what I want to do and I think it could be tricky.

I have a column with a list of percentages based on

    =IFERROR(CONCATENATE(ROUND(100*[@[Calls Offered]]/([@[Calls Forecasted]]*1.05),1),"-",ROUND    (100*[@[Calls
Offered]]/([@[Calls Forecasted]]*0.95),1),"%"),"N/A")

the first cell ended up being 99.6 - 110.1%

To decide if the forecast is effective the actual value must be between .95 and 1.05 of the forecast, so percentage wise, the left number must be less than 100 % and the right number must be greater than 100%. So the first cell satisfies this situation.

What I want to do is to count how many of these cells in the column would satisfy this.

I have a feeling that it would be easier to split the forecast variance into two sets so that I can check each columns that way, but it is important that these numbers are on the same sell, for visual purpose.

I think this could be tricky because what I tried to do first is to show the percentages using "concat" but I could not change the formatting of the number into a percentage for some reason. So, I forced in a % symbol within the function so that it looks nicer.

Answer 1

With your data starting in column A at row 2, this should calculate the over/under.

=SUMPRODUCT((--LEFT(SUBSTITUTE(A2:INDEX(A:A, MATCH("žžž",A:A )), " ", REPT(" ", 9)), 9)<100)*
            (--RIGHT(SUBSTITUTE(A2:INDEX(A:A, MATCH("žžž",A:A )), " ", REPT(" ", 9)), 9)>1))