I am going to convert different costs during life time of a product to Net Present Worth (NPW), consisting Maintenance and Rehabilitation costs (M&Rj) and Salvage Value (SV), according to the following formula: [URL] .......
SV occurs only once at the end of life time, it's NPW in Excel can be calculated as following:
=PV(i discount, AP,0, SV,1)
But M&Rj costs occurs J time during life cycle, and formulating manually their mathematical formula most times takes much time. Exactly what should I write in the cell to get their total NPW? In other words, how can I get the following formula by Excel functions: [URL] .......
Reminder: NPW= FV( 1/(1+i Discount))^n
FV: future value
i Discount: discount rate
n: numbers of years (periods)
I'm dealing with Projects that have an X amount of Units used in a certain period of time. The amount of units are evenly spread among the time the project runs. The problem is that many projects start in 1 month and end in another.
To clarify, the code in my head is like this:
Total Units in project: 80
project starting on 12 jan, project ran 19 days in Jan. Project ending on 14 feb, project ran 14 days in Feb. Cost of a unit in Jan = 200
Ofcourse, the code needs to be smart, since the data will be filled in to a table, so the code has to change automatic every time a new project is entered. I cant use pivots 'cause of a standard format sheet.I have the costs per month in a raw data sheet. Start date and end dates in different cells next to each other.
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
I am using Excel 2010 on windows 7 PC. I work for a railroad and one of my coworkers gets data once a week about how many carloads we ship to a certain company. They save each week in its own worksheet so at the end of the year they will have 52 worksheets worth of carload data. What they would like to do is take these 52 worksheets and then combine this data onto one worksheet so they can see all of it going from week 1 to week 52.
Here is what 1 week's worth of data looks like...
Here is what the 52 week summary page looks like...
And here is what the summary page looks like with all of the data on it....
Although we have compiled all of this data, it was done by copying and pasting data from 52 different worksheets (took some time as you could imagine). So, my coworker asked me if I knew of a faster way to do this. After spending multiple hours on the net researching this and trying a bunch of different options (Data Consolidation, Summary/Array Functions, and varying Macros) I have not been able to produce a worksheet that takes all of the data and displays it like the last picture above. Because I only know the very basics of excel I'm sure I'm probably doing something wrong. Trying to decipher the code for macros hasn't been easy and although I got data consolidation to work it was adding up all of the numbers instead of taking them from one sheet and placing them on another with all of the worksheets' data side by side.
I need to compare dates that include both the date and time of day, to find when the day changes.
I have a large list of dates (also includes the time of day in the same cell). There are specific values in the next column that correspond to that date. Some of the values that are too great and have to be changed. This I have figured out with some help from this forum. When a value is changed I need to add together all the values for the corresponding day, some could be above and some could be below.
I'm developing up a spreadsheet to conduct a feasibility on a 50 unit townhouse development and I was wondering if there was any way of allocating costs across the project without physically typing in the data in each month? (96 months in cashflow!)
ie: - In a row headed "Roads & Drains" can I allocate the say $500,000 worth of works across the months by using a formula or other methods?
My current method is very time consuming when I want to conduct a sensitivity analysis,
I am trying to put together a sheet to calculate item cost after shipping and taxes. To be honest I may have complicated the whole thing but what I have is two order volumes that need to be changed to calculate the most cost effective solution. For example: Item 1 (a1) is $40 and item 2 (a2) is $10, if I was to order 100 (a3) of (a1) and 50 (a4) of (a2), then multiply them in (a5) and (a6) to give me total order values without taxes etc. I then have (a7) which is the shipping (a8) the import tax and (a9) the VAT. What I am stuck on is how to work out how much a1 and a2 costs me per item, if I was to order 100 of each then I think it would be simpler by calculating the total cost and divide by 100 then again by 25% which is the cost difference between $40 and $10. So although I may have gone around the houses on this one, I want to be able to change cells a3 and a4 and then see how much it is costing per item after shipping and taxes.
I am having trouble calculating multiple full packages in the attached spread sheet, at the moment the formula is multiplying the number of packages by the price of one full package instead of looking for the correct price for that number of packages. 2014 rates checker formula.xlsx
Plan numbers are contained in Row 1 (1518, etc). Options are listed in Column A (L101, L102, etc). I want to add the costs of each option, resulting in the total and each option code only listed once, with that total, below each plan. Example:.......
I am trying to set up a spreadsheet to record business costs, I have a column [A] and [B] with start times and finish times respectively. [C] is the shift length say 10 hours but I always get an error if its a night shift as the start time is invariably later than the finish time. Is there a way around this without inputting the dates into the cells as I am trying to not duplicate data entry.
I'm an entry level Excel user who is looking for a spread sheet to print multiple pages for tagging pallets in a warehouse. The pallets will contain the address of the customer and the number the pallet is.
Example 30 pallets would be 1 of 30 then print 2 0f 30 then print 3 of 30 then print
how I can put in a varible pallet total and have it print the all sheets up to the given number?
See attached. (I added in some arrows for the first month so it is easier to follow the logic)
I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.
Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.
Essentially, I need the formula to return the correct amount that is being paid, even across different years.
I don't know if my formula can be modified or a new formula would be more efficient.
(This post originated from Commercial Services, which I just requested be removed from that forum)
I am trying to set up an IF formula for real estate operating costs. I am guessing that this is going to work best with an IF statement.
(Sample of the spreadsheet is attached)
I know this is wrong but it expresses what I am trying to do. =IF(C5="Gross",B10=following year of B9 with month/Day being 1/1), if not B10=the folling year of B9 with same month/day)
1Cell C5 can either be Gross or Net from a drop down list. 2Cell B9 lists 3/1/2008 3If cell C5 is gross, cell B10 should be the following year from cell B9 but with starting with 1/1/??? As the month and day(year start). B11 would then be the following year from B10 (following month/day pattern). 4If Cell C5 is NET, cell B10 should be the following year from cell B9 using the same month and day 3/1/????
I need to change cells to reflect new costs. Then have these costs roll into new totals. I attached a worksheet to demonstrate how it works. The numbers in gray should be adjustable. The numbers in tan are summing up incorrectly with my "SUMIF" formula.
I have downloaded .csv files with my phone call costs. I want to compare phone companies, so I need to calculate the h:mm:ss amounts to $ based on flagfall and rate per 30 seconds. I can open the .csv files in Excel but I do not know anything about Excel.
I have a repetitive task that needs to loop effectively. The only things that change each time are the start Columns ("C:C") next moves to ("D:D"), this then continues down the different columns, until it reaches the end of the columns.
The other thing that changes is the formula initially it starts at RC[-1]&"",""&RC. Next time the numbers will be plus one, so RC[-2]&"",""&RC.
The last thing that moves is the "B1:B" & LR on the next step will move to "C1:C" & LR.
Basically a loop with variables in the loop. Is this possible? Below is the code I have begun to write but I could be writing this for ever, over and over again.
For example: From To .................. P1.........P4 P2.........P4 P1.........P5 P1.........P4 P3.........P5 P2.........P4 P2.........P4 P2.........P3
There are hundreds of sheets like this with more complex panel numbers (there are 70 to 80 panels). I want to count the number of times each panel is linked with another panel i.e the number of times its name is mentioned in front of it. This may look like as follows.
I have a workbook populated with values in 3 columns. The values of the second column group rows into larger segments. I want to pull one row for each different value onto a different sheet.
example: 1. apple 2. apple 3. apple 4. apple
How do I only pull one row of apples onto another sheet without copying by hand? I have attached an live example. I want to pull one row for each classification (column 2) named on the sheet without copy/pasting each one.
I have attached an example spreadsheet with two tabs. The first tab (Matrix) shows raw data scores - there are 28 measures in Column A and Participants in Row 1. There are always 28 measures but participants can vary. What I need to happen is to sort each measure in a Lineup tab i.e. rank all the participants listed in the Matrix tab from lowest score to highest score for each of the 28 measures. Obviously this can be done manually but a VBA/macro solution will be so much faster and easier. Unfortunately I have to keep the data in this format so the macro will need to transpose the data to get into the formt in the Lineup tab.