Summing Cells To Get Difference (subtracting) Only If Both Are Not Blank?
Oct 19, 2012
I have a spreadsheet of clients' body weights each week which goes in a column and to the right of it I have a "diff" which I want to show weight gain/loss.
So for a client with weigh-ins the week of 10/1, 10/8, and 10/15 the columns will be:
10/1(V1)--Diff(W1)--10/8(X1)--Diff(Y1)--10/15(Z1)...and so on.
If I have weigh-ins for each week, no problem. I would just have to: =SUM(X1-V1) to get the change in weight from the week of 10/1 to 10/8. Say the client weighed 200 on 10/1 (V2) and 195 on 10/8 (X2), the difference (or "diff" (Y2)) would be -5.
But let's say the client didn't weigh-in on 10/1. The difference in weight is going to be 195. Or if they weighed-in on 10/1 but didn't on 10/8 it's going to read -200.
So I have two cells (say V2 & X2) that if one of them is blank, I want the "diff" to read "N/A" or at least leave the cell blank.
If I have the following: =SUM(E2:F2) - G2). So lets say that E2 has 50, F2 has 60 which = 110. Now I want to take that 110 and subtract it from what is in G2 and put the result in H2.
I am having trouble finding the difference between times. I have two cells, A1, A2. Times will be placed in there each day. A1 will have the first time and A2 will have a later time that day. i.e. A1 12:25AM, A2 2:45AM. A3 would have the formula. In this case I am looking for an answer of 2:00 (2hrs).
My second issue will be times when I have A1 11:20pm and A2 1:20am. I can't seem to get it to work.
It displays #VALUE! instead of the weighted sum I want. The reason for this is some of the cells are blank. If cells O72 and P72 are blank, how would I get it to sum just K72 and N72?
Other rows might have different missing cells, so the formula would have to work for different combinations of missing cells, but the weights will stay the same.
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.
A B C D Date ReceivedApproved DateDenied DateTurn Time 1/1/14 1/13/14 9
Turntime is calculated with this formula: =IF(A4<>"",IF(COUNT(B3,C3),NETWORKDAYS(A3,IF(B3<>"",B3,C3)),""),"")
What I need is to not count the first day it is received in the calculation for the Turn Time. So in this instance I need the Turn Time to say 8 instead of 9. If I just subtract a 1 then I get an error if the Approved Date or the Denied Date are not filled in yet.
I had a question considering a workbook that I am working on for overtime, a different one than I posted before. On my workbook, I have vacation leave, sick leave, and overtime to figure in. I want to be able to have a choice to choose which you would like to keep at the end of the week. Such as if you were to work 8 on Mon, 8 hours on Tues, 10 hours on Wed, and then take vacation leave on Thur, for 8 hours, and then work 6 hours on friday, I need a way to choose whether to be paid for the overtime and vacation time, or to subtract two hours from my overtime and add it to my regular time, i.e. 40 hours, or subtract two hours from my leave time and add it to my regular time. I know this sounds complicated, it is at least to me.
I was trying to subtract two arrays of data and putting the result in some cells but with no success unfortunately. I'm relatively new to VBA and I'm just starting now to make calculations with arrays so excuse my little knowledge. The arrays that I'm trying to subtract are from row 1 to 250 and m and n variables have the number of the columns. Here is my routine:
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)
But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.
I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B.
If there is no value in column B, then I want that row in Column C to just stay blank.
However, I want to ignore the "" cells and the truly blank cells... However, I think all of them will have "" since I have this formula in all of the ones I'm putting in the range:
[Code]....
How do I go about getting these results into a named range so I can use it on validation since validation only seems to ignore truly blank cells and not the "" ones.
I am working on a sheet which calculates payroll. I have a list of people in column A, and in column B, i have their different professionnal status. Their wages vary according to their professionnal status and are in another column C. How can i sum up the wages of only certain people, based on their common professionnal status in column B?
I have a 2 lists of manually entered values where the values in each row are related. They are related in the sense that if the difference between them is equal to or less than, a given amount then the cell containing one value is colored Blue. e.g.
COl A holds Value x while COl B holds value y. I need to check which value is entered in columns A and B and if a given difference or greater exists colour the cell in COl B. For example the relationship relevant to a COL A value of 21.0 is 11.0.
So, I enter the following;
A1 B1
21.0 13.0 <----------------No color Blue
I enter new values
21.0 9.5 <----------------B1 is blue.........................
I am trying to sum up a list of cells that have formulas attached so if there is no number in the formula it shows up as #N/A how do I make it so wherever there is an #N/A it equals 0?
I am attempting to use the if function that looks at a cell to see if it is a certain value and if so it adds the value of the cell above with another cell.
Here is the formula in cell AH26: =IF(G26=Variables!$F$4,AH25+AF26,AH25)
the problem is if the G25 did not equal Variables!F4 then AH25 is basically zero or the formula so when cell G26 does equal Variables!F4 then AH26 is supposed to sum AH25 with AF26 but AH25 cell's contents are a formula. How do I get it to ignore the formula and add AF26 with zero instead of the formula?
I am working on a sum formula for a column and I only want to sum if the value is a multiple of 15 or likewise if the value divided by 15 is an integer. I know that I could use several sumif statements to accomplish this, but there isn't really a maximum value. Is there a simpler way to accomplish this? I am comfortable enough with VBA so that is definitely an option as well.
I'm using the following code to filter a particular range(it works perfectly fine). However I need to SUM Column 'L' once the data has be filtered. and place the result in the LASTS populated cell in Column L. At the moment I am selecting all the data in the column even the data that has been filtered out.
I want to sum two cells, doesn't sound that difficult but I want to be able to change one of the cells over time. Hence, if B1 is the total sum cell, and A1 is what I fill in. B1 starts at 0, and if I fill something in in A1, B1 will sum itself and A1 (Unfortunately this gives me a circular reference). So what I'm looking for is some way to use paste special automatically in the formula B1 I guess. It is possible and how?
I'm trying to find the difference two cells within a row to another row.
I'm using time values i.e 17:07 and 14:53 and in the third cell I'd like to get a result that shows me a plus/minus of the differences.
I know by looking what math to apply to that particular cell. Is there a way to do a formula to get the results no matter if they are plus or minus. without having to change the formula back and for on if i know it'll be increasing or decreasing?
I have to calculate the difference of two cells in an excel sheet if the first cell value is greater than the second one. If the first cell value is smaller than the second one then the difference should be shown as zero.
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
I have sporadic cell values in a column. I will be describing the lamen logic I use to fill in these blanks manually. Find the first nonblank cell in the column, then look for the next nonblank cell in the column. I count the number of blank cells in between. If even # of blank cells, then I give the first half of the blanks the value of the first nonblank cell and the second half of the blank cells the value of the next nonblanck. If odd # of blank cells, then I do the same with the exception that the odd cell that falls in the middle will be randomly designated the value of the either first or second nonblank cell.
