Uniformity Calculation

Discuss the spreadsheet application
Post Reply
Bison
Posts: 4
Joined: Tue Dec 11, 2018 9:29 pm

Uniformity Calculation

Post by Bison »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Uniformity Calculation

Post by Zizi64 »

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.
Bison
Posts: 4
Joined: Tue Dec 11, 2018 9:29 pm

Re: Uniformity Calculation

Post by Bison »

Here it is
Attachments
Uniformity.ods
(10.28 KiB) Downloaded 63 times
Open Office 4.1.5
Windows 10 HOME
Bison
Posts: 4
Joined: Tue Dec 11, 2018 9:29 pm

Re: Uniformity Calculation

Post by Bison »

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
Bison
Posts: 4
Joined: Tue Dec 11, 2018 9:29 pm

Re: Uniformity Calculation

Post by Bison »

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
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Uniformity Calculation

Post by RusselB »

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.
Post Reply