Average Formula When Sample Size Changes
Jan 17, 2008
I am computing the average of anywhere from 5-10 numbers. When the numbers are not inserted there is nothing left in the cell. The problem is I get a value that is close to the average but not the real average. (Also the cells I am using are N37:Q41 for the first 5 samples which is constant the next 5 are not always constant W37:Z41) Here is the formula I am using:
=IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z38),IF((W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z39),IF(W41="",AVERAGE(N37:Q41,W37:Z40),AVERAGE(N37:Q41,W37:Z41))))))
I have the same problem with a standard deviation formula and the same group of numbers (I get something close but a few points off).
=IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37:Z38),IF((W40="")+(W41=""),STDEV(N37:Q41,W37:Z39),IF(W41="",STDEV(N37:Q41,W37:Z40),STDEV(N37:Q41,W37:Z41))))))
View 9 Replies
ADVERTISEMENT
Apr 16, 2009
How you do use excel to calculate a sample size using the population, error limit, confidence level and upper error limit rates?
View 9 Replies
View Related
Feb 13, 2014
I am working on a sampling tool in Excel which is based on what a user enters into a column. The user enters observation codes (text) in column A beginning at row 2 and going down to however many they have. I already have formulas which determine the sample size needed based on the number of nonblank values in that column; the resulting sample size is in G4. I'm thinking that I will need to have a form with a button module to select the random sample from the values in col A. This way Excel isn't running the module while the user is entering the values. The resulting sample can be pasted into a separate column or sheet.
View 3 Replies
View Related
Mar 24, 2014
I'd like to work out the average of a set of data which changes length and position. It's probably easier if I attach a workbook.
The different sets of data are split by blank cells so they can be distinguished.
View 6 Replies
View Related
Jan 7, 2008
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
View 9 Replies
View Related
Oct 10, 2007
I want to pull a sample of 15 numbers from column A, the catch is that the contents of column B cannot match once the sample is pulled. How do I do this.
View 9 Replies
View Related
May 20, 2014
Need to get tile size from product description.
Find attached , within file have specified expected output.
codes cleaner.xls
View 4 Replies
View Related
Nov 27, 2007
It is useful to use lookup function to find the student name. However, if the table array size is variable
(say, a new student enrolled and the table array will be $A$2:$B$7), the table array size will be changed
and we need to change the vlookup function. How to avoid it? Can we use some kind of variable for the
See attached file( book1)
View 9 Replies
View Related
Mar 3, 2009
How to insert the sample mean symbol, so that the "bar" is centered over x?
View 5 Replies
View Related
Apr 24, 2013
I am working in excel 2007. I have been using the COUNTIFS function to pull out some summary stats from my data. For my data I have sample ID's which are a letter followed by a number e.g. A12, A20, A39, A50. I have 40 columns of variables I am looking at which for each sample ID have either a value of 0 or a numerical number. For each column, I want to count firstly any samples that are greater than 0 (i.e. they have a numerical value) AND if the sample ID differs. This is because I have multiple samples per ID and so I want to know how many different ID's have a numerical value.
Is this at all possible in Excel? I do not know all of the functions very well, so I cannot find anything that will allow me to look at differences in the samples ID.
View 2 Replies
View Related
Dec 5, 2009
I have an excel sheet with the following sample data on it.
Name Age Sex Height
Ray 33 M 5' 8''
Sind 29 F 5'4''
Ralph 3 M 2' 1"
Ryan 1 M 1' 2"
I have managed to extract this info onto a form named Form1 which has 4 list boxes in which the above info is displayed in the same format.
To be more precise
Listbox1 shows all the Names,
Listbox2 shows Age
List box3 shows Sex and
Listbox4 shows height respectively.
What i want to do is, When i select an item on Listbox1, all corresponding enteries on Listbox2, 3 and 4 should also be simultaneously highlighted,
For example if I select the third data Ralph on the ListBox1, then ListBox2 should highlight 3,
Listbox3 should highlight M and
Listbox4 should highlight 2' 1" simultaneously.
View 9 Replies
View Related
Sep 23, 2006
I have stored production data in Column A. I want 5 data randomly pulled from that column with click of a button. Would this Happen?. I have attached an excel sheet.
View 2 Replies
View Related
Sep 1, 2009
how to reduce formula size/improve excel efficiency?
I have a really complicated formula with lots of COUNTAs, VLOOKUPs, IFs, etc etc etc which returns a variety of values, ZEROs (for blank cells being referenced), and #NAs.
I would like to turn all ZEROs and #NAs into ""s without repeating this super long formula over and over.
I am curently using:
=IF(or(ISBLANK([superlongformula]),ISNA([superlongformula])),"",[superlongformula])
View 9 Replies
View Related
Oct 24, 2007
using the ttest formula function
View 2 Replies
View Related
Aug 4, 2009
I have a sample of aproxomatly 30,000 records, and I am working to do some data analysis on it.
I am comparing multiple fields using sumproduct(), but if I go above two criteria, the calculation time becomes incredibly long. Is there a faster way to preform these large comparisons, or am I stuck watching my Excel lag out for 5 min every time I recalculate the sheet?
View 5 Replies
View Related
Oct 4, 2009
I've coded a macro that can randomly sample out rows of data from a sheet. This macro is used to sample out certain rows from a master sheet full of data (which has 48 columns) based on user names (a column titled LAST_UPDATE_NAME). For Example (ref. wkb attached), if for 5 users named Alastor, Catherine, Emma, Julie and Victor for whom i've to choose data from the Dump sheet.
I need to design a module in which if i input the no. of samples to be chosen against each name, then that many number of rows has to be chosen for that particular name and copied to a new sheet (probably such sheets can be renamed with the usernames). Also i want to consolidate the copied data from the multiple sheets and consolidate them to one sheet.
View 4 Replies
View Related
Nov 5, 2009
Can anyone explain clearly (using layman terms) about how to go about randomly choosing specific number of rows from a set of autofiltered records?
For e.g., If there are 1000 rows of data in a worksheet and after applying autofilter to certain column(s) [one or more] say i get some 75 rows. Then how can x no. of rows be chosen where x can be input by the user or calculated based on a certain percentage? Say if out of these 75 filtered rows, 8 rows have to be chosen randomly - where 8 can be input by the user or also be calculated as 10% of 75.
Also, is it possible to choose these x records from the filtered set of rows without actually copying them to another sheet?
View 12 Replies
View Related
Dec 3, 2009
I have a file that samples performance on a cpu. The output shows the sampling date and time in column A. This column changes depending on how long I run the file. No matter how long the column, I only want to pull out the sample date and time 20 times. So if I had data in column A with 300 rows, I want to divide 300 by 20 and pull those 20 numbers and put them in column B in chronological order. Same thing if I had 4000 rows in column A, I only want 20 samples.
View 5 Replies
View Related
Aug 3, 2012
Any code that can extract the alpha numeric values from sample spreadsheet below?
Where in col A "SCn" is extracted (or copied) and then pasted in col H?
n = 1 to 99
That is - from this:
A
B
C
D
E
F
G
H
I
J
K
L
[Code] .........
To this:
A
B
C
D
E
F
G
H
I
J
K
L
[Code] ......
There are thousands of rows to extract the alpha numeric values from.
View 6 Replies
View Related
Mar 23, 2009
Please see attached two books,
The first book is a report that is generated on my reporting systeM (Report I use everyday)
The second report is the one that I have created myself using a macro (macro report)
I have to manually manipulate this report everyday and extract some information from it to
a custom report(macro report) that we use everyday.
I have created a macro for this - please see my code below.
I need some help with some small issues I am having,(Highlighted in yellow)
1) I need to include a Total line, but the size of the report varies everyday from say
around 30 rows to over a 100 rows. Is there a way i can make the total always 2 rows below
the report, please see attached for an example.
2) I need to hardcord a formula in on Column F and G. But again the report varies in size,
the columns do not change but there could be lots more rows. how do i take this into
account
The formula i need to hard code into column F is
=(IF(($D$60>=0)*AND($D$60<=5000000),0.0009))+IF(($D$60<=10000000)*AND($D$60>5000000),0.0007
)+IF($D$60>10000000,0.0005)
D60 in the above formula is just the total of all the other months summed together.
For Column E the formula is just =F*E But again the size in the report varies.
View 13 Replies
View Related
Nov 10, 2009
I have been set a task to do and I wonder if you could point me in the right direction.
Task - extract 2000 emails from a 6000 email database
The 2000 emails have to be proportionate to the original database.
e.g.
The main database has the emails plus town and employee size ranges
Column A - Emails Column B - Town Column C - Employees
So if Column B states that 50% of the entire database is from one town, then my extracted emails must also have half from that town (1000).
Also there are around 5 employee ranges and so they need to also be proportionate to those percentages too in the final extraction.
View 10 Replies
View Related
Aug 14, 2013
im trying to create a simple random sample from my data set of size n=100 and then i need to repeat that step a 1000 times to make a new data set that i can transfer over to stata. Everything I have seen on the internet involves generating random numbers which isnt what i want. I need to be able to generate a random sample.
Currently my excel spreadsheet consists of 4 columns with 200 entries in each column.
View 4 Replies
View Related
Feb 24, 2014
I have a workbook name Sample and I want the code to run when its opened and it should check the first sheet "Sheet1" in all the workbooks in the folder C:FolderTemp.
And if the first sheet name is not "Sheet1" then just ignore that workbook and move to next workbooks. If the first sheet name is "Sheet1" in a workbook then store the value present in cell B1 in Sample workbook "Sheet1".
So if there are 5 workbooks in the folder C:FolderTemp and two of them hasn't got first sheet named "Sheet1" then display value of B1 in rest of the 3 sheets to the "Sheet1" of "Sample workbook" one after another.
View 14 Replies
View Related
Feb 22, 2007
Option Explicit
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
'(2)
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
'(4)
Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup)
'(5) Give the control a caption................
I have created several spreadsheet with customized menus. In step 5 when I type
a period the options for selecting Caption does not appear. In step 6 the 'Control' word didn't appear in list. Sometimes the options in step 6 do appear BUT not the option for Controls. I have created spreadsheets with menus that work but the only way I could do it was to cut and paste from the website and then modify it.
View 3 Replies
View Related
Jul 8, 2009
I need to show the size of a project in a cell based on it's number of hours, e.g.
Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE
How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?
View 3 Replies
View Related
Nov 7, 2007
Cell: F6 is to represent "average weekly offering".
D2:D53: represents the Total Given Year To Date.
I need a Weekly Average for Offering, so is it possible to have a formula that can show the average weekly offering in cell F6?
View 10 Replies
View Related
Mar 31, 2007
I'm building a yearly workbook with multiple worksheets (i.e. one for each month, quarterly reports and one final yearly report). My question is that although I can have the data transfer easily to my quarterly and year end report sheets, I can't seem to figure out how to filter my data and then calculate an average based on personnel averages in the various categories I have set up.
My example is that I want to be able filter my personnel shifts (i.e. 7am, 3pm, 11pm, etc) and then set up a formula which can for example calculate a column of data and average it only based on the employees working that particular shift. My formula should adjust for the shift time I pick.
View 10 Replies
View Related
Feb 3, 2009
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.
Then, I need the following logic as VB macro.
for row = 6
F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")
View 2 Replies
View Related
Oct 26, 2012
I have a large amount of data in 2 columns which I want to statistically average to see if a relationship exists between two variables.
Column A: Contains Iodine concentrations ranging between 0 and 22000 ppm.
Column B: Contains Nitrate (NO3) concentrations ranging between 0 and 22 %.
I have over 7500 rows of data. In theory there is an endless amount of data but it will all be kept in these two columns
What I want to do is create average values of NO3 based on set Iodine ranges.
e.g. I want to know the average NO3 grade for the cells where the Iodine concentration is between 0 and 99 ppm.
I want to be able to calculate this average based initially on 100ppm Iodine bins but dont know what clause I would need to add into the formula or if this is even possible.
I would also like to be able to edit the bin size to see if I can discern a better relationship between the two using different ranges. As such I can create the bins in a separate column but again I don't know the best way to do this to make it work well in a formula.
View 1 Replies
View Related
Jul 8, 2013
I have data regarding the percentage of QC for the team.
I need to find out the individual's average QC scores from the list already available.
The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage
View 3 Replies
View Related
