View Full Version : Solved: Help Basics Input Data

09-25-2008, 03:31 PM
Hi I'm new to the vba coding inside excel and I have a program I need to make. The program needs to take input data of varying sizes and calculate averages and ranges for each set, then calculate the averages of these averages and ranges as Xbar and Rbar. My difficulty is that when I try to set this up it finds the first averages without trouble but then doesnt find the Xbar or Rbar if the data is under the max. Any guidance or help would be great. Thank you

09-28-2008, 12:42 AM
Can you post your workbook or a workbook with similar data so we can see what you are trying to do?

09-28-2008, 07:47 AM
Currently I am trying to figure out a way to set up a table. I made the subroutine to calculate all the formulas and information for input data, but I want to make it easy so the user knows where to insert the data. I was thinking of maybe a prompt asking the size (rows. columns) of the table then input their data.

09-28-2008, 08:27 AM
You can also create master copy with predefined size of the rows/columns with protected cells leaving unprotected them ones you need to be filled in by the user.

09-28-2008, 10:28 AM
Issue I'm having is I have a subroutine that scans any input data from A1 on. It scans for part of the equations by row and another part by columns. I was hoping to pop in some headers and sample numbers to make it more user friendly for the people to enter their data. Only issue with this is if I have headers and row numbers it calculates my data wrong. It counts the empty row because of that sample number and counts extra columns due to the headers. I wish I could tell it to only scan for the range excluding column A and Row 1.

09-28-2008, 11:04 AM
Sample workbook will help us to help you better and quicker :)

09-28-2008, 01:25 PM
Basically it does the calculations for any input information from A2 on. I left a simple amount of data in there as a test. I just want to be able to have headers and sample numbers to go for 30 sets and 10 observations of data. That way when they open the file they see where to input their data and then say they put in 20 sets it still gives the correct data and doesnt count the last 10 rows that are empty except for a description "sample 21".
Any help would be great, thank you.

09-28-2008, 01:57 PM
If you can save it as .xls as I'v got Office 2003.

09-28-2008, 02:03 PM
crap it didnt change the format, I'll readd

09-28-2008, 02:06 PM
there we go

09-28-2008, 03:46 PM
Can you explain me how many rows and columns are in one set?

How many sets are in one observation and how sets are placed?

The best would be sample before inserting and after inserting data.

09-28-2008, 04:03 PM
A set in this case is a row of information. The number of data points in that set are observations. So say I got a 2 sets of data as follows:
Set1 (6 5 4 7 9)
Set2( 4 5 6 1 6)
These are 2 sets and each have 5 observations. My code scans for total rows of input data, takes average of each then averages them for Xbar. Then also takes range of each and averages them for Rbar. Then it uses these values as well as the observation count (# of columns) to determine upper and lower control limits for a graph I will be making. Problem I have is I want it to ignore both row 1 and column A when searching for values. When I add headers it screws up my control limits because it thinks that there is a column of information just from the stupid header. And for the rows it will mess up when I label set1.
Samples Observ1 Observ2 Observ3 Observ4

1 3 5 3
2 1 2 6
3 4 3 2
In the above it would give me incorrect values because it thinks that there is a 4th row and a 4th column.

09-28-2008, 04:07 PM
If you can save it as .xls as I'v got Office 2003.

You can download the MS Compatibility Pack (http://www.microsoft.com/downloads/details.aspx?FamilyId=941B3470-3AE9-4AEE-8F43-C6BB74CD1466&displaylang=en) to let you read 2007 files in 2003

09-29-2008, 12:29 AM
Check attachment and let me know if that's what you wanted.

BTW. xld thanks for the link

09-29-2008, 11:40 AM
The values wont come out right with that setup. I was able to adjust it to get Xbar and Rbar coming out right but the control limits think there are 10 columns of data. basically I am just trying to set it up where Column A would just be labeled samples in A1 then go down the column counting from 1-30. Then row A(B1, C1,D1,E1,F1,G1,H1,I1,K1) would say Observation 1-10. This creates my data entry area for the user. So they know where to place their data. Then I just want to have my subroutine that I posted ignore column A and row 1. Problem is that it believes since I have a label or header to then count that row or column as if it has a value. So even when I only put 4 rows it acts like I have 30 and same with columns going up to 10.

09-29-2008, 11:44 AM
Can you tell me how your results should be calculated?

For each column (observation) separately or for all of them at once??

09-29-2008, 12:14 PM
This file has it working right for Xbar and Rbar and I have the setup in the sheet how I am trying to do it. Problem is for the UCL and LCL for both X and R it is counting those empty columns due to their headers. Is there a way to have it ignore that?

09-29-2008, 12:20 PM
The data is calculated based on what the user inputs. So say they put 20 rows of data with 5 observations each. Then it will row by row take the average of each and then average all of those. That is the Xbar. Then it will take the range of each and average those ranges for Rbar. Then using Xbar and Rbar it finds the UCL and LCL with the equations in the code and calling upon a value from an array corresponding to the number of Observations we were given. I made it where it calulates for any size and counts the entered data to do the calculations. I got it to where it ignores the extra row numbers but it still thinks there are 10 columns due to the header.

09-29-2008, 01:40 PM
try now :)

09-29-2008, 02:41 PM
Sweet man we got it. I had to change the equation for Rbar to Lrow-1 instead of Lcolumn and then for each of the control limit equations I changed it to Lcol - 2. Now it works perfectly. It has been working both when there are more observations then rows and when more rows then observations. Ty for all the help.