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:
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.
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.
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.
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.
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.
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.
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 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?
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.
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?
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.
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
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.
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.
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.
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?
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?
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.
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.
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.
