How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??
View 4 Repliescalculating the number of calls logged in a shift, based on a 3 shift system in a day. shift 1 from 08h00 to 16h00, shift 2 16h00 to 24h00, and shift 3 00h00 to 08h00. each shift receives a number of calls based on the time the call was submitted.
View 2 Replies View Relatedhow would I calculate the number of hours in a shift after midnight. What I want to say is that the number of hours after midnight on a friday shift to be taken off from friday total hours and added to a saturday shift.
I would like to use vlookup to calculate the number of shift per week for each agent
View 10 Replies View RelatedI have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
For instance I have a formula such as =sum(A1:A9). But imagine that the range is not always till row 9 but depends on the total number of rows that are in the table. How can I change that 9 so that it takes a number equal to the total rows in the table?
The final formula I want is not that simple one. It is actually
=INDEX($F$1:$F$1047,MATCH(1,INDEX(($G$1:$G$1047=G2)*($L$1:$L$1047<>"47200")*($L$1:$L$1047<>"47700"),0),0)).
I want to change 1047 by the relevant number of rows. Also, I do know how to do it in VBA, but I have a problem with the length of the formula there (not that one, but other bigger than that)
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
I have a Training Matrix with "Main", "Leavers" and "Reports" sheets. (can email it but can't upload it here; it's 534kb and I can't make it smaller)
In the "Reports" sheet I need to fill "Site Procedural Training" - "Number of people trained in period".
The periods are :
25/12/11 - 31/03/12
01/04/12 - 30/06/12
01/07/12 - 29/09/12
30/09/12 - 29/12/12
The source of data will be the "Main" and "Leavers". The data are dates in these sheets; each date = 1
The formula needs to look in to "Main" and "Leavers" and if it finds a date which fits in the required period in "Reports" then return it as 1. If it finds 300 dates then returns 300.
I'm looking for a formula, which says if a number is within a range, perform a calculation. I have a table of ranges (0-6000,6001-10000,10000-12000, and so on). My formula looks at the current month cumulative balance, and inserts the value into the line of the range it falls into. However, the formula now contains lots of nested "IF" statements, due to the number of ranges I have. Also, if my cummulative balance is 6005 for example, but was 5005 in the previous month, I would like to see 95 in the 0-6000 row, and 5 in the 6000-10000 row. I making a complete nonsense of trying to explain myself, so I'll attach a quick example of what I'd like to see.
View 4 Replies View RelatedI have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
I am trying to delete a group of four cells and have the remaining block shift up. My code is below and crashes on the red. What is the right code to get this to work?
Code:
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = Range("G1").Value Then
Range(ActiveCell:ActiveCell.Resize(0, 3)).Delete Shift:=xlUp
Exit Do
End If
Loop
How do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.
IE: Range Name "MyRange" = A10:A100
I insert a new row at A4 then "MyRange" = A11:A101
I want it to stay at A10:A100.
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies View RelatedI need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
=COUNTIF(D$5:D$8,OR("a*","b*","c*"))
I have a range of 30 cells in a row. From that row there are groups of cells that have data that might be 1-7 cells populated in a row and in between these groups are blank cells in the row. I need to see what is the highest number of cells in a continuous row that contain data, what the mode is for continuous cell counts, and what is the average value for the data from the continuous cells. The average is a nice to have but not entirely necessary as most cell data will be fairly similar and I can get a close enough average just by knowing the average number of cells that contain data in a row. I have to do this calculation for about 1000 rows of individual data so its important a formula fit in one row.
Almost need a count function to count starting with each cell and then stopping at the first no no value it finds. Then it would show those counts and I could run a formula to pick the highest count and also show the mode of counts.
I will let the experts at it!, my weak mind could not think this one through. I thought something to do with MATCH for a while but no go from what I could do.
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedI am wondering if it is possible to create a formula in a cell that will divide whatever number is entered into that cell by a set or fixed number.
ie. I enter the number 9 in the cell and when I press enter, the 9 is instantly divided by 15 and the answer is displayed in the cell where I just typed the 9.
I have one excel sheet with columns from A to LZ and number of rows.
Each column have only letter (one of A,B,C,D,E). This if for weekly table:
COLUMN define: Mon-Sun
ROWS define: work type (A B C or D)
For example;
COLUMNS: Su-Mo-Tu-We-Th-Fr-Sa-Su-Mo-......
ROWS: A -A - B - B - B - B - B -C - C - ......till column LZ.
Issue:
I want a formula for any row, from which, I can calculate (for that particular row) number of B's on Mo-Tu right from column A till LZ.
I need a formula that can look at to cells, and if they are both the same pick cell A1 but if one of the cells has a higher amount than the other pick that one.
so the formula would do the following
look at the amount in A1 and B1 if A1 is higher than B1 input A1, if B1 is higher than A1 input B1 but if A1 = B1 then input A1,
I can't seem to make SumIF or Vlookup do what I want here.
I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column.
If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3)
Period Target Actual %Target
1 74 68 91.9%
2 81 71 87.7%
3 76 87 114.5%
4 76 68 89.5%
5 71 89 125.4%
6 69 81 117.4%
in cell a1 i have 34
i have a formula
INDEX($A$20:$A$40,MAX(IF($E$20:$G$40=a1,ROW($E$20:$G$40)))))
is there any way i can make this formula look for
34 and the reverse of itself which is 43 also
In cell A1 = 15/06/2006 (dd/mm/yyyy)
What is the formula to find how many no. of days in month provided in cell A1.
I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below.
week39
ABCDEF45939SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046039SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046139SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046239SCHTERR-WIEDEMANNDCCDIST-MUSCHANY0463 SCH Total 14042.2146439SCITERR-SULLIVANDCCDIST-MUSCHANY15587.1546539SCITERR-SULLIVANDCCDIST-MUSCHANY7217.2246639SCITERR-SULLIVANDCCDIST-MUSCHANY5316.6246739SCITERR-SULLIVANDCCDIST-MUSCHANY2720.6946839SCITERR-SULLIVANDCCDIST-MUSCHANY520.446939SCITERR-SULLIVANDCCDIST-MUSCHANY195.7147039SCITERR-SULLIVANDCCDIST-MUSCHANY047139SCITERR-SULLIVANDCCDIST-MUSCHANY0472 SCI Total 31557.7947339SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY4190.7147439SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3219.7647539SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3187.147639SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2209.3747739SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2132.7547839SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2034.56
Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471)
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I have two date columns, a start date and end date. Another columns tells me the number of days between these two columns. It may return 45 days which actually straddle 3 different months.
My aim is to identify between this date range how many days in Apr 14. Then in the next column howmany days in May 14 etc
So on the spreadsheet Id have 12 columns for each month of the year. in Apr 14 for each row between the given date range I want it to tell me how many days are actually in April. Id then copy that formula into the May 14 column.
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.
A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)
Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)
Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.
i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.
View 9 Replies View RelatedI need help with a formula to calculate average number of rebills per client.
I don't know how to get excel to add the number of unique client in a given row. Example
Column A
Client 1
Client 1
Client 1
Client 2
Client 2
Client 2
Client 3
Client 4
Client 4
Client 4
Client 4
Client 5
Formula needs to calculate number of Unique clients. In this case, the answer is 5, but how can I get excel to calculate for me?
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View 19 Replies View Related