I have a list of grade from 2~4 (i have long list but for sample i made it short). I want to get average of all grades one by one as well as by making some groups. Like 2,3, "2~4" (all groups) then "3~4". All the things were going nicely with a drop down menu. But when i put average on grde "3~4" only then the problem occurred. Although I've done my work but still I think there must be some improvements.....
View 6 RepliesI have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
I'm trying to calculate the average of every five rows. I have a formula =Average(A1:A5) in cell B1. When I do an autofill in B2, the formula would read =Average(A2:A6). What I actually want it to be is =Average(A6:A10), increments of 5. I don't know what i'm doing wrong. I have also tried selecting all the B rows and doing autofill and that still doesn't work.
View 3 Replies View RelatedI would like to get a monthly average between the first of the month & the end of month of a handful of percentages collected during the month for individual projects.
View 10 Replies View RelatedI'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.
View 8 Replies View RelatedThis time I have a situation like this:
Column A - Dates
Column B - Equipment Type
Column C - Load Start Time
Column D - Load End Time
I need to do some statistical analysis (average, mode, medium, st dev) on the load times, which is load start time - load end time. As far as I know, I can't subtract ranges (column d i column c) within Averageifs function. I assume I would need to use an array function for this.
I would like to create a shortcut to averaging ranges. The ranges vary in size so I want to be able to click on a cell. Press Ctrl + W and automatically average the cells above it. I know how to get the short cuts, I just can't figure out the proper code to be able to average based on different sizes. Let me know if you need a better explanation./
View 3 Replies View RelatedI would like to average data between 0600 and 2200 hours, between two date ranges, such as July 1st and July 31st.
Capture.JPG
Using Excel 2010.
I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried
=AVERAGEIFS(DAYS360(A:A,B:B),A:A,">5/1/2012",A:A,"5/1/2012",A:A,"
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies View RelatedIm trying to make a vlookup so I can say find a record where column A is >4, B is =2, C is 1.
Example - The lookup would find these 2 ranges.
7
2
3
2
6
4
[code]....
I have set following ranges:
[Code] ....
I use above rngData1 as follows:
[Code] ....
How can I use all above ranges in For Each statement?
For example For Each rngRow In .Range(rngData1+rngData2+rngData3+rngData4).Rows
I am trying to create an IF function and how to create it for multiple ranges. The for the following criteria is what I am trying to work with: If a checking balance is $500 dollars or below return "low", If it is between 500 and 2000 return "Medium", and IF greater than 2000 return "high"
View 2 Replies View Relatedlook at the formulas in column cells G28..G33 and tell me where I went wrong.I have attached the file.
View 2 Replies View RelatedI am trying to combine three IF formulas that depend on ranges that vary. I think the attached sheet does a much better job of explaining what I am looking for than I can do.
View 3 Replies View RelatedQuick question: When using "With" is it possible to specify 2 ranges, or will I need to do 2 different with blocks? The reason I say not using Union is because I need my ranges seperate. I run 1 set of commands on Range 1, and 1 set of commands on both Range 1 and 2.
View 3 Replies View RelatedI am trying to use an ActiveX CommandButton on Sheet40 to do the following:
Sheet40 (E31:AN39) = sum of (E31:AN39) for sheets 6-15 PROVIDED that cell D3= "y" in those sheets
So the steps are:
(1) Among sheets 6-15 select those in which cell D3 = "y"
(2) Set the range of (E31:AN39) in sheet40 to the sum of the same range in the selected sheets
ie cell E31 = sum of cells E31 in selected sheets... cell E32 same... cell AN39 same
I have 2 ranges which on their own seem to work fine (I have check by just putting ".Select" at the end of both of them). The issue is that I want to apply the same border format to both ranges, however, when I try to put them together in a "With" statement it includes all of the range from the top to the bottom and not just the 2 two ranges. Ranges being used:
View 2 Replies View RelatedI am getting a NEXT without FOR error message when i try to run this code. This is the portion of sub im having trouble with.. been trying to work it out but to no avail. I thought that i might have to do Next Ccell, though I got the same error.
View 2 Replies View RelatedI am trying set up my spreadsheet so that when a number is put into one column. The next column is filled with a number that will correspond to the other number specified from a range of data.
Distance Sheet no.
55
68
93
135
186
So in the sheet No. column I want it to read the number in the Distance and put in the Corresponding sheet number.
So if:
Sheet 1 is : a distance of 1 though 20
Sheet 2 is : a distance of 20 though 40
Sheet 3 is : a distance of 40 though 60
Sheet 4 is : a distance of 60 though 80
Sheet 5 is : a distance of 80 though 100
Sheet 6 is : a distance of 100 though 120
Sheet 7 is : a distance of 120 though 140
Sheet 8 is : a distance of 140 though 160
Sheet 9 is : a distance of 160 though 180
Sheet 10 is : a distance of 180 though 200
How can this be automated without writing a ton of nested if statements.
This is an example but in reality I have 29 "sheets" with different ranges to separate.
Countif: Is there a way to have a single criteria (a persons name) and multiple ranges example: a6:a10 c6:c10 and recieve the sum of that criteria and ranges?? I know there is I just cant get it.
I need to count a persons name entered in multiple ranges (cells or areas) on the same worksheet. I cannot make one big range because i will need to do the same for the b6:b10 but for a different "need" the a colum and c colum bieng "completed" the b colum bieng "not completed". I have tried =countif(a6:a10 + c6:c10, cell_with _persons_name) and for obvious reasons that wont work,
The else part is the part i cant get right.
Sub LabSumPTOHide()
If Sheets("Labor Summary").Range("A8").Value Like "Function*"
Then Sheets("Labor Summary").Rows("7:49").Hidden = True Else Sheets("Labor Summary").Range("9:15,24:49").Hidden = True
End Sub
I have this piece of code which just copies every 3rd cell in column B from sheet1 and pastes it going down column A in sheet2. This works fine for me.
Code:
Sub Macro1()
Dim FirstCopy As Integer
Dim FirstPaste As Integer
FirstPaste = 1
For FirstCopy = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 3
If Range("B" & FirstCopy) "" Then
Range("B" & FirstCopy).Select
Selection.Copy
[code]....
However, now I want to copy every third cell from columns B, D, and F in sheet1 and paste them going down columns A, B, and C in sheet2. When I run the code below I am receiving this error: "Compile error: Wrong number of arguments or invalid property assignment"
Code:
Sub Macro1()
Dim FirstCopy As Integer
Dim FirstPaste As Integer
FirstPaste = 1
For FirstCopy = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 3
[code]....
How to use COUNTIF when there are multiple criteria. For eg. I have 3 columns, and I want to count the # of employees in each row if all 3 criteria in columns C, D, and E hold true.
What I'm looking for is that it ADDS the values, when 2 or more ranges and criteria are met. I don't think it's possible through the SUMIF because it allows "range,criteria,sum_range." But I know there's another way around, if so, please demostrate in a basic formula.
Like.. I have something on A:A (name) and B:B (points), and the formula must meet the 2 ranges & criterias (name & points) to add the values in C:C (money).
# of Project Names that are under Status=Contract (Amount in $’s ) & # of Trks
# of Project Names that are under Status=V Verbal (Amount in $’s) & # of Trks
# of Project Names that are under Status=No ($ Amount lost) & # of Trks lost
# of Project Name that are Lost to (Grandview) that are under Status=No ($ Amount lost) & # of Trks lost
Must be able to add more lines & have instructions so I can do it myself for the future.