VBA Subtracting Ranges ...
Jan 20, 2009
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
View 14 Replies
ADVERTISEMENT
Jun 9, 2014
Simplifying the following formula:
'=((F17-F17)*D17)+((F17-F16)*D16)+((F17-F15)*D15)+((F17-F14)*D14)+((F17-F13)*D13)+((F17-F12)*D12)
Its basically the sum product of column F time column D...however, all the F values are first subtracted from the last F value; Obviously I could just have an extra column to calculate all the new "F" values the issue with that is that I want to be able to drag down the formula and so the value F17 changes for each last value in column F...
View 5 Replies
View Related
Dec 14, 2008
if lic# in column a = lic # in column e, i would like to use the corresponding avg wt in column f to subtract column b.
would i have to use if and vlookup function? (i.e. =if(a=vlookup(....), f-c)?).
View 9 Replies
View Related
Oct 31, 2006
I have added a worksheet to a workbook, a co-worker will populate another worksheet within is workbook. The worksheet the co-work populates is a schedule. On my worksheet
I've set a possible units per day value, then pull from the schedule, starting from the top, the order that can be done in this time frame.
My problem is every so often in the schedule she will total the units in a cell (the units for each order are in one column and the total is also in this column, and this has to be this way for others in the plant). My formula checkes the sum of the units in this column, but it includes the cell that has the total units. This is the only cell that has a value in it on this row. I was trying think of some way to check the order number column for a blank, and if it was then subtract the value in the cell that has the units total in it.
View 10 Replies
View Related
Jan 30, 2007
I have three cells... A1= entry time & H1 exit time...both are formated as such Custom #0":"00 so i don't have to enter the colon with every entry and exit time... my issue is that i want the third cell to count the minutes in cell A1 & H1...
View 9 Replies
View Related
Feb 15, 2008
I saw this answered somewhere on here but really didn't understand.
I'm trying to set up a sheet for my hours worked for the week.
I have the total per day in H2 through H6 In I2 through I6 I have an accumulative total for the days, so assuming I work 8 hours a day, I2 would be 8:00 I3 would be 16:00 I4 would be 24:00.
In J2 through J5, I'd like to have the weekly accululative worked hours subtracted from 40 so that the total is what I have left work until I hit 40.
View 9 Replies
View Related
Jan 22, 2009
Start time is 2300 in A1
End time is 0100 (the next day) in B1
Difference should read 120 minutes in C1
I know this is possible, I think I've thought myself into circles on something that shouldn't be hard. Everything I've tried is giving me negative numbers and at some point I've tried using the +1 behind B1 to represent the next day.
Here are some of the formulas I've found throughout various posts and used.
=B1-A1+IF(A1>B1,1)
=B1+(A1>B1+1)-A2
=IF(A1
View 9 Replies
View Related
Mar 27, 2009
What would the formula be for subtracting two dates to get the numbers of years, months? I cannot get my formula to work.
Also, can Excel take a telephone number such as (555)-555-5555 can place the phone number in another field such as 555-555-5555?
View 9 Replies
View Related
Jun 25, 2009
Here's a simple one guys. How do I subtract 7:30 AM from 4:00 PM to give me 8.5 hours? At the moment it returns either 8:30 AM or (formatted Number - General):
0.3541667
View 9 Replies
View Related
Sep 24, 2006
I am tryinig to use an if statement but I don't have it correct. If columns E, F, and G are not null I want it to subtract 3 from the Total column. Right now it is subtracting from G.
Sub SubtractPoints()
Dim I As Long, subpoint As Long
With ActiveSheet
subpoint = . Range("h65536").End(xlUp).Row
For I = subpoint To 2 Step -1
If Not .Range("E" & I) = "" And Not .Range("F" & I) = "" And Not .Range("G" & I) = "" Then
.Range("H" & I) = "=RC[1]-3"
End If
Next
End With
End Sub
View 2 Replies
View Related
Feb 5, 2014
I have a total number of hours I want to subtract from. Example in cell e3 I will have 800 hours. In cell f3 I have the number 2 (to represent 2 workers at 40 hours, so that should total 80). I then want f3 to multiply by 40, then that total subtract from the 800 hours in cell e3. Basically I want to be able to put 2 workers in a cell, have that multiply by 40 and minus from the 800 hours in cell e3. I want that to carry on for cell g3, etc.
View 1 Replies
View Related
Jul 3, 2014
I'm trying to program a spreadsheet for staff attendance at a school. In the spreadsheet the staff member has personal days, sick time, and sick time in the bank (sick time rolled over from last year). When they use sick time, it needs to come out of the bank first, until it reaches zero, then it should move on to the sick time accumulated for this year.
Staff attendance WIP.xlsx
On the left side is the staff name, sick time, personal days, and bank. Then are the school days for July (if a staff member uses sick time/personal day it is placed under the date it was used). On the far right is the total remaining amount that the staff member has left.
The formula I have for cell AF4 is: =MAX(0,G4-SUM(H4:AC4)) so that the cell will not go below zero.
The formula I have for cell AG4 is: =E4-(COUNTIF(H4:AC4,"P")+(COUNTIF(H4:AC4,"HP")/2)) So that when P for personal day is entered, it will take a whole day, and when HP is entered a half a personal day is subtracted.
I need to figure out a formula for cell AD4. I've tried a couple of formulas I've seen on other posts, but they weren't for a series of numbers like this. And when I put them into this particular spreadsheet, it would subtract the bank until zero, move on to the 'sick time' cell and subtract the SUM of all the days sick rather than subtracting what was left when the 'Bank' reached zero.
View 1 Replies
View Related
Oct 22, 2007
Is it possible to subtract a number from a column letter? I'm trying to create a sheet that does some helpful calculations. In order for me to finish it without a macro i need to be able to subtract a number in a cell from a column letter. Example F-5=A, The end i'm trying to achieve is to get a sum range to move left and right in this data set based on that number.
View 5 Replies
View Related
May 13, 2009
I am using excel 2003 and I am trying to subtract 20 days from the date in cell A3 in order to give me a date for me to order materials. However all I get is #NAME? in the cell.
View 4 Replies
View Related
May 28, 2009
Subtracting 1 day when the weekday is a Thursday. I need with the formula above (on AA2):
View 2 Replies
View Related
Sep 8, 2009
I'm making a table for myself to keep an eye on my hours worked every week. What i want is to be able to enter the start time and the end time and for Excel to find the time difference inbetween (not numerical mathematical difference) also i need to subtract a half hour from the time entered for monday thru thurs.
View 2 Replies
View Related
Dec 10, 2013
I have a large set of data in an array of many cells. I need to subtract a fixed amount from each of these cells without changing the location of the cells as there are many different references too them Is there a way to do this?
View 3 Replies
View Related
Apr 17, 2014
im looking for a macro that will take away the free stock value from the values in the following columns until the value reaches 0 then subtract from the next column with a value in it until there is no more left in the free stock to subtract. if the value reaches 0 it can be blank.
so for example as shown in the image attached cell b2 shows 150 in free stock, then the next column with a value in is d2 which shows 100 so takeaway until this reaches 0 which leaves 50 and this can be shown as blank now, then move to the next column which is e2 and this shows 30 so this can now be blank also, finally f2 shows 50 so minus the remainder from the original 150 which 20 and this should now be 30.
my data is in a pivot table so a paste special to another sheet may also be necessary. The amount of columns could also vary due it being a pivot table.
View 5 Replies
View Related
Feb 22, 2010
On my worksheet I have a date of birth formated yyyy-mm-dd. On my form, I want to be able to identify anyone younger than 16. I tried subtracting from today's date on the worksheet, but that didn't work too well for me.
View 2 Replies
View Related
May 30, 2007
I have a weekending date. I would like to subtract X amount of weeks from this date and end up with the weekending date less the weeks I subtracted.
View 10 Replies
View Related
Oct 7, 2008
In A1 I have this: $9.50.
In A2 I want to display the result of multiplying A1 with 10% and subtracting the result from A1.
I've tried this in A2: =SUM(A1*10%)-SUM(A1)
But it displays a negative number: -$8.55
I need it to display: $8.55
View 3 Replies
View Related
Apr 17, 2012
I am trying to subtract 45min from the time 00:05, but the result i am getting is 0:40 which is wrong.
I am using below formula.
=TEXT(ABS(B3-$A$3),"-h:mm")
Actual timeTime minusResults00:0500:45-0:40
View 1 Replies
View Related
Jun 18, 2012
I am trying to work out the response times to an event that my staff attend ( such as a fire alarm)
In C1 will be the time the event was called in, 23:55
In E1 will be the arrival time, 23:59
In F1 I want to show the time taken to arrive on site (response time)
This seems straightforward until the arrival time goes into the next day, such as 00:05 and this is when I have the problem
To make it more complicated, sometimes there is no need for an arrival time to be entered for some events ,with the end user leaving it blank or putting "NA" or "na" etc into the cell
I have tried the below formula which works to a point but leave me with an ######### error if the time is after midnight
=IF(E1="NA",0,IF(E1="n/a",0,E1-C1))
View 9 Replies
View Related
Jun 10, 2013
I am trying to calculate employees hours of work from information provided to myself.
Each employee is contracted to work 40 hours per week. The information provided to myself is in the format - Employee has worked 39.30 This is in fact 39 hrs 30 minutes. Obviously when I subtract 39.30 from 40.00 I get 0.7 and not 30 minutes. I tried formatting the cell containing these figures to hh:mm but it comes out at 15.30 & 15.00 respectively because it is over 24 hrs. How do I convert the digital number (0.7) to minutes I.e. 30 minutes.
I have tried to convert the 40 hrs into minutes but because the hours are greater than 24 then I struggle.
View 9 Replies
View Related
Sep 25, 2013
If in Cell A2 I have a function that updates it's number value automtically (i.e. From 5 to 7) and in cell B2 I have a value I wanted subtracted to the value Cell A2 updated by. Is this task possible?
In the I.e example, cell A2 had an updated value by 2, will it be possible to subtract that value in Cell B2 and continue doing so if Cell A2 kept updating?
View 4 Replies
View Related
Aug 14, 2007
If I have a date in Cell A1, what formula do I need to put in cell B1 to subtract 6 months.
eg If A1 = 14/08/2007 I want B1 to read 14/02/2007 and not 12/02/2007 which it reads when subtracting half a ywears worth of days
View 9 Replies
View Related
Jan 23, 2008
I have the formula in my spreadsheet to compute time. It works only if the time in B1 is greater then the time in A1. I would to know if there is a formula to compute time with either negative or postive answer. For example if a carrier was set to load their papers at 12:02pm and ended up loading early at 11:50am I would like the result in C1 to be -12 or (12).
Currently I am using the formula: =HOUR(B1-A1)*6+MINUTE(B1-A1). Like I said, it is all good until someone loads early and then I get a "#NUM!"
Are there any other formula's that I can use or is there a simple modification to the formula I am using?
View 9 Replies
View Related
Mar 10, 2008
1) To be able to get the current date in the format of 3/10/2008
2) To be able to take dates from 2 cells and get an integer value for the number of days between the two dates
View 9 Replies
View Related
Jul 30, 2008
I am making a timesheet
I have
Start Time, End Time, Break, Hours Worked. Then on the right hand side of my spreadsheet I started playing around with the current time etc.
I want to work out the time left in a working day(like a countdown), based on a variable number of hours of work in a day (here it is 7 hours) excl. breaksie. 7+breaktimeso I need 7+break - 'hours worked' to get hours and mins left
I worked out how to get hours worked easily enough,
=J58-LOOKUP(TODAY(),A:A,D:D)-LOOKUP(TODAY(),A:A,B:B)
where J58 is a cell that has the current time in it and D and B are the columns with the break time and start time in them.
Hours Worked01:59:48Current Time11:49:4807:00:00Break time: 00:30:0007:30:00
I am trying to subtract the hours worked (1:59:48 in this case) from 7:30:00. The hours worked can be updated every second using F9.
I know it's something to do with negativer times because of dates etc but I don't know what to do to make it work.
View 34 Replies
View Related
Jan 21, 2010
I have some intraday date that looks like this, hope the formatting comes thru when i post this....anyhow, the second column is TIME (ie 15:25 15:30, etc...5 minute time intervals for SP500 stock data. I want to have a column that simply subtracts row 2 column b (time column) from row 1 column b.....when i do this i get a #value....I know this must be possible to get a result i need....ie 15:30 - 15:25 = 5
Do i need to reformat the TIME column into something different? ....
View 9 Replies
View Related
