Apache OpenOffice Community Forum

So I am trying to find a way to do the following calculation. Cells B10;E26 which consist of 100 cells are averaged in cell H10. H9 calculates 110% of H10 and H11 calculates 90% of H10. I need to find out how many cells in B10;E26 fall within that window of 90% to 110% of the average value. A simple example would be if 10 cells of the hundred were the value 90 and 90 cells were the value 200 the average (H10) would be 189. H9 would read 207.9 and H11 would read 170.1. The 10 cells with the value 90 would fall outside of the 10%+ and 10%- band and would give a uniformity of 90%.

Please upload your ODF type sample file here.

Here it is

So working with it I'm thinking I could use countif to see how many fall outside of the 10% +- range and use that number to get uniformity

So I used =countif(B10:E26:">"&G10)+countif(B10:E26;"<"&I10) and I'm getting the number I need to use to calculate the value I want. There's probably a more efficient way but that's how its working now.

Based on what you are currently using, the COUNTIFS function (not documented in the help file, but does have a help balloon when entering it) might work better... or the SUMPRODUCT function could be a possibility, as both of them allow multiple ranges, alone or with a comparator (like you are currently using in the COUNTIF functions)