I am wanting to calc the yrs of serv in different age ranges for example
Start Age = 17
Yrs in Serv = 25
Age to date = 43
Band 1 (21 yrs) IF(start age is =41,age to date - 41,0) =2
Band 1 = worked 4yrs from an age of 17 to 21
Band 2 = worked 18yrs from an age of 22 to 40
Band 3 = worked 2yrs from an age of 41+
I am stuggling with the formula to calc Band 2.
I have column A1:A100 which contains times which vehicles are off site and B1:B100 which contains times when they are back on site I need a formula to look in these colums and tell me for any time ie 21:30 how many vehicles I have off site.
View 9 Replies View RelatedI am facing an issue right now to extract time band from a column. Below is the data from where I have to extract Time Band only. I tried different things but its diifcult for me to extract time slot.
New Rate - 19:30 - 19:59 -
Effective From 1st May - 00:00 - 00:59 -
- 07:00 - 07:59 - Branding
Morning - 00:00 - 01:00 -
Night - 21:00 - 22:00 -
LPT - 23:00 - 00:00 -
- 22:00 - 23:00 - PT
- 22:00 - 23:00 - BEFORE EP
The output should be like this
19:30 - 19:59
00:00 - 00:59
07:00 - 07:59
00:00 - 01:00
21:00 - 22:00
23:00 - 00:00
22:00 - 23:00
22:00 - 23:00
The issue is.. I have a locked spreadsheet and one of the column has a formula in it i.e column H +column I - column J for each particular row.
wen the user inserts a new row the formula is not entered automatically to the new cell in the column which has the formula.
could someone tel me how to write a code for calculating the cell when they click on the button..
basically i have thought of having a button which the user clicks to populate the whole column. But i am not getting the syntax right..
I have 2 columns.
Column E has the amount of time each day.
Column G is where I want the Sum to be displayed.
I need Column G to do a =Sum(E4:E33), but if anywhere in between E4 and E33 there is a period of 5 days where it totals 0 then I want column G to re-add from the first day where there is a amount in it.
Ex: E4 to E10 totals 7 (1 per day), from E11 to E15 totals 0, from E16 to E33 totals 18 (1 per day again).
In Column G at G10 it should total 7, at G11 it should be 6, G12 5 etc until it hits G15 where it sees the 5 previous days nothing was entered so it enters a value of 0, because G15 is set to 0, G16 will start adding from E16 till E33, until somewhere down the line it sees another period of 5 days where it adds up to 0 again.
Spreadsheet tracks race times for cross country races and needs to be able to
calculate improvement time in mm:ss (or zero if less than previous race).
Also,it would be nice to be able to enter times without using colons or have
to reference the field as h:mm:ss. The race result will always be mm:ss and
the last two digits will always be seconds, the result will never be more
than 60 minutes so hours are a real bother.
A1 (8:52, m:ss) - A2 (8:40, mm:ss) should = 0.12 (12 seconds)
Presently I am using the following: =TEXT(A2-A1,"h:mm:ss"), I only want to
display m:ss, hours are meanlingless
I have 3 columns of dates (A1, B1, C1).
Several different scenarios:
1. If A1 is blank, but B1 & C1 are populated, I want to put the result in
D1.
2. If B1 is blank, but A1 & C1 are populated, I want to put the result in
D1.
3. If C1 is blank, but A1 & B1 are populated, I want to put the result in
D1.
4. If A1 & B1 are blank, I want to put "N/A" in D1.
5. If A1 & C1 are blank, I want to put "N/A" in D1.
6. If B1 & C1 are blank, I want to put "N/A" in D1.
Why are there 3 date fields you ask, the powers that be want it that way,
How to calculate ANI in one cell? The only way I can currently find the result is to build an amortisation schedule and then divide the sum of the outstandings by the payment frequency.
I'd like to avoid building the amort.
Is there any way of opening a workbook via VBA that will set the calculation to xlCalculationManual BEFORE the workbook calculates anything on opening.
The purpose: I'm opening the workbook remotely from an Access document and there are quite a few formulas in the workbook that take a few seconds to calculate. I have a modeless UserForm that displays from the Workbook_Open event which I want to get displayed before all the calculations take place so the user has something pretty to look at while he/she is waiting. But it seems that the workbook performs a calculation before running the Open event code.
I want to find out how many hours, in total, relate to "consolidation 1, 2, 3; Lease Renewal 1,2 3 etc. What formula can I use. I am working on a "sumif" but it's not working out for me.
Hours
1 Consolidation 4.75
3 External Sublea
3 Consolidation 2
3 Special Project
2 External Sublea
1 Lease Renewal w
1 New Lease w/o T 4
1 New Lease w/o T 1
1 External Sublea
3 Lease Renewal w 1
2 Lease Renewal w .5
i have a problem that i have been trying to get over for about a week now.
i need to calculate a lease commission, with an extensive amount of variables.
first i need to find the length of the total term which should be anywhere between 1 to 10 years.
then on a annual basis i need to define how many months are billable in that year.
which gives me to variables to account for there, which are
A= initial free months, non paying
B = the last month of last year may only be a half year
i think i have worked that out pretty successfully, so next i need to calculate the rent for each year period. with several variables
a= the rent can be caculated :
-by per month basis
- by annual basis
- by a per square foot basis
b= next in relation to annual rent operating expenses may also be calculated in the annual rent number also by the same variables, however it may or not be calcuated into the number depending on the lease.
c. this is where i am at now, and its killing me. i need to account for rent adjustments for each year.
rent adjustments can start from either the lease start date or the date that rent starts which would be after the lease start if free rent is granted.
then the adjustments will continue through the end of the term and be implimented every x number of months.
the value of the adjustments will either be a percentage of the first years rent usually 3-5 %
or per sf, per month, or just flat rate per year. but it will escalate each year.
for example year 5 is x amount of ajustment from year 4.
i am finding difficulty in finding an annual value of the original lease term in relation to this date series. expecially if the adjustment periods leave a remainder carring over to the next year, or if their are several adjustments in one given year.
any help would be appriciated on this.... i know its pretty complicated, and i have rewritten this code about 30 different ways , i am at a loss right now.
if you think you may want to see my file let me know and i can post it
I am trying to populate Column BB based on data in BA. Explanation: If BA2 is less than 1, BB2 should equal "00-01". This should run until BA is blank. Sample value in BA: 6.68.
I'll paste in my code which is causing a run time error 13 type mismatch error starting with the line in red below. I wonder if I am even close?
Dim m As Long
For m = 2 To Range("BA65536").End(xlUp).Row
Select Case Cells(m, 53).Value
Case Is < 1 'line where error starts
Cells(m, 54) = "00 - 01"
Case 1.1 To 2
Cells(m, 54) = "01 - 02"
Case 2.1 To 3
Cells(m, 54) = "02 - 03"
Case 3.1 To 4
Cells(m, 54) = "03 - 04"
Case 4.1 To 5
Cells(m, 54) = "04 - 05"
Case Else
Cells(m, 54) = ">5"
End Select
Next m
I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.
View 8 Replies View RelatedI need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View Relateddefinitions for static ranges and dynamic ranges?
View 9 Replies View RelatedI have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
In the attached file, I have variable range in column A:B, column C:D and in column E:F
I want a macro to do the following:
Start with sheet "A", select the available range in column A
then copy and paste in the sheet "B" but with all the cell values added with the value in H1.
Then in sheet A, simply copy the available range in column B and paste it in sheet B
Do the same until column F in sheet A. Pastespecial if it is odd column. simple past it is even column.
I know the macro code for the simple paste. But I am struggling with the paste special code.
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
I am trying to locate a named range cell in my VBA macro, but find that VBA will only allow me to specify the actual cell eg. Range("E2").
The piece of code I am using below is
Sheets("sheetname").Select
Range("E2").End(xlDown).Offset(1, 0).Select
I would like to use
Range("namerange").End(xlDown).Offset(1, 0).Select
Do i need to Dim or specify the name range in any particular way.
I'm trying to get a formula to give and yes or no answer if a cell is between two numbers.
Example: Cell AB9 = 100.2 in cell AB11 would be a formula that would answer "Yes" because it is between 96.8 and 100.4. Any number outside this range would answer "No."
I am trying to make a UDF that searches for a header, grabs everything under the header, and pulls it somewhere else. My UDF has three parameters:
1) Output_Range: the named range where the parameters will be pulled to
2) Header: the header to search for in order to copy the data underneath it
3) WorkbookName: the name of the workbook to search in
It looks like this:
VB:
Function LoadParameters(Output_Range As Range, Header, WorkbookName As String)
MyTimer = Timer
'Defining the variables.
Dim HeaderCell, HeaderCellEnd, HeaderRange, Output_Range
[Code]....
I'm attempting to do a sumproduct with different dimensioned ranges. I've read multiple posts that say that all ranges in a sumproduct must have the same dimensions. I've tried different commands (index/match/lookup, etc) with no luck. Perhaps someone can come up with a solution for me....
View 7 Replies View RelatedI 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'm trying to create a Formula where the User has to select 2 Ranges and then a calculation is done for each corresponding cell.
I thought about using arrays or ranges but I'm quite lost.
[Code] ....
My Second approach would be to use Arrays
[Code] .....
This is what I thought about so far but it does not work. I guess because I have to Idea how to tell VBA to use the Interestrates Range for each corresponding cell.
In sheet x the range("d2:d20") will change from time to time. I've created a button that should copy this range("d2:d20") to sheet called summary and there to the next empty column. So the variable lstcol (dim = long) is the last empty column in summary. I'm definitely not using properly the range method.
View 8 Replies View RelatedI have 2 ranges with values, and I want to use countif to see of there's a same value in each of these ranges.
I would like to perform a calculation with each cell in a range from 2 worksheets and place the result in a range on a third worksheet.
I thought I could use the For Each Cell in Range construct but I don't see how to reference the two ranges in a nested pair of For loops. I am sure there must be an simple/elegant way to do this.
I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do:
Select cell C192
Select Shift and R-Arrow so that C192 and D192 are selected
Select Ctrl, Shift and Up Arrow so that C1:D192 are selected
Select Shift and Down Arrow so that C2:D192 are selected
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10.
I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>.
Sample .......
I have two Ranges, which I view as Arrays or Matrices, I want to subtract the two (which do not overlap or union in any way) to get a new third range of the differene. This new range should be the same size, and can be considered a difference of each cell in the range. Note both ranges are already identical in size.
Any help solving this, searched up and down for hours.
Note; Im trying to avoid looping, as this might become incredibly inefficient if the ranges grow to big, looking for alternative that is faster, someone said maybe excel.evaluate