Uniformity Calculation
Uniformity Calculation
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%.
Open Office 4.1.5
Windows 10 HOME
Windows 10 HOME
Re: Uniformity Calculation
Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Uniformity Calculation
Here it is
- Attachments
-
- Uniformity.ods
- (10.28 KiB) Downloaded 68 times
Open Office 4.1.5
Windows 10 HOME
Windows 10 HOME
Re: Uniformity Calculation
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
Open Office 4.1.5
Windows 10 HOME
Windows 10 HOME
Re: Uniformity Calculation
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.
Open Office 4.1.5
Windows 10 HOME
Windows 10 HOME
Re: Uniformity Calculation
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)
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.