Uniformity Calculation

Discuss the spreadsheet application

Uniformity Calculation

Postby Bison » Tue Dec 11, 2018 9:39 pm

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

Re: Uniformity Calculation

Postby Zizi64 » Tue Dec 11, 2018 9:49 pm

Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Uniformity Calculation

Postby Bison » Tue Dec 11, 2018 9:52 pm

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

Re: Uniformity Calculation

Postby Bison » Tue Dec 11, 2018 10:29 pm

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

Postby Bison » Tue Dec 11, 2018 11:21 pm

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

Re: Uniformity Calculation

Postby RusselB » Tue Dec 11, 2018 11:30 pm

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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4931
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: Villeroy and 25 guests