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.
View 2 RepliesI'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.
I have a workbook, everyday 2 new worksheets get added to reflect the previous day's work. How can I reflect in a formula to go to next sheet instead of the conventional naming 'Sheet1' G7?
View 6 Replies View RelatedI've searched the boards & haven't found anything as uncomplicated as what I'm looking for. I know I could use Sumproduct, but for something this simple is there anything I can do with Sumif? This in effect is my worksheet.
AB
1AD200=SUMIF('Sheet2’!B:B, A1, 'Sheet2’!C:C)
All I want to do is change the criteria to be the equivalent of A1 OR B2 (ie checking the range for either match. No way to do that in a sumif? I'm looking for the least complicated, easiest to adjust/edit formula possible.
I have a dropdown list (it is tied to a sumif function and it works perfectly) but I also have some images and when a different selection from the dropdown list is selected I want the images to change. Is this possible using sumif or is it possible at all?
View 2 Replies View RelatedI have 3 columns
Column A is a list of suppliers
Column B is a list of purchase orders (there may be several purchase orders for each supplier)
Column C is a list of values
I am trying to show the top 10 suppliers in terms of value, not sure on the best way to go about it
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
View 5 Replies View RelatedPlan 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:.......
View 2 Replies View RelatedIs there a possibility to make a sum off the amount of a cell (C59 in my example) of all the sheets in the workbook ?
just like =sum(sheet1!c59,sheet2!c59) but with all sheets.
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 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.
View 2 Replies View RelatedSee 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/????
How can I create a formula that will reflect a % value within that range?
(Example)
Input cell = 129 this value can be changed between 0 and 800
A1 = 0
A2 = 800
(Range) 0 to 800 = 100%
What would the percent value equal?
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.
View 4 Replies View RelatedI'm looking for a function (I'm thinking 'SUMIF') that will search through column A of Sheet 1 and add column B of Sheet 1 when the cell in column A contains one of the strings listed in column A of Sheet 2. I can only find information when searching for single strings or cells.
View 8 Replies View RelatedI want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
I have a master worksheet with due dates for various forms to be completed. These forms are on separate worksheets within the workbook.
I'd like to highlight the master date entry in red if the form hasn't been completed by the due date, but also highlight in green when it has been done.
I've seen it done, and had the formula on a couple documents, but had a loss of data. What I'm looking for is the formula to enter into a cell for copying the name of the worksheet into that cell.
View 6 Replies View RelatedI am trying to sum the details from sheet2 in sheet3. I am currently using a plain formula to sum-up the values. However, create a indirect and vlookup function to sum the values.
In sheet3, I have to sum the values from sheet2 based on the contacts. Sheet1, sheet2 and Sheet3 are connected and any update(insert,delete.. etc) will be made in sheet 1 only. Sheet2 automatically updates based on sheet1. Similarly, Sheet3 should also update based on sheet1 and sum the values based on contacts from sheet2.
I have added the formula which I am currently using in the sheet.
My goal is to have changes done to one sheet reflect on to another to make things much more easy to do. Below is a picture of what I am dealing with. ( I covered some personal information)
pic of excel.jpg I want to be able to plug in something or delete it and have it reflect to all other sheets. Also I would like to be able to plug in some information and have it export itself based on a specific information. For example, I would want all Newark,NJ contacts to be place in the Newark list automatically.
I have a spreadsheet that references another spreadsheet, however the other spreadsheet will get updated from time to time. When it's updated the file name changes to reflect the revision:
"C:Projects9-022_Tunisia1_Design9-022-R00-Inst_Database.xls"
When this file is revised the "...R001..." will increment with each revision but the rest of the file will stay the same.
Sometimes I have to reference a file in another directory:
"C:Projects9-030_Brazil1 Design9-030-R00-Inst_Database.xls"
I was planning on placing information in the following cells of this spreadsheet ("C:Projects9-022_Tunisia1_Design9-022-R001-Cause_&_Effects.xls):
Cell A1 - Project Number (i.e. 09-022, or 09-030)
Cell A2 - Project Name (i.e. Tunisia, or Brazil)
Cell A3 - File Rev Number (i.e. 1, 2, 3, etc.)
I was thinking my "INDIRECT.EXE" would look something like this:
=INDIRECT.EXE("'C:Projects"&A1&"1_Design["&A1&"_"&A2&"-R00"&A3&"-Cause_&_Effects.XLS]Index'!Q5")
The value on the spreadsheet tab "Index" that I'm referencing in cell Q5, is "FG-5901" but all I'm getting in the spreadsheet were this formula resides, is an error "#NAME?"
Part of my problem is that I don't fully understand the function of the "&" symbol in the formula. Sometimes I've seen it placed on both sides of a cell reference (i.e. &A1&), and other time only at the being (i.e. &A1).
I have two different Google Sheets Workbooks;
I need that for ie: changes made on C3 of Workbook 1, reflect on E23 of Workbook 2.
I am needing to reflect some of my work in a waterfall chart, but do not have that as an option in my list of charts. Is there a down load I can get?
View 2 Replies View RelatedI've compiled data into a spreadsheet which indicates the number of homes sold over the course of 3 years (from 1/1/05 to now) along with their price per square foot, sale price, year built, bed/bath count, and other pertinent factors regarding each property.
I've extrapolated graphs from these in the past which indicated the trends in each category over time (i.e.: Average quarterly price per square foot over time, and so forth).
The data range I'm working with now, however, is from an area of my region so small, that the number of units sold over the past few years is sporadic. Whereas it's normally 100 to 1000 units that comprise my data, now it's only 15 units.
This has created gaps in my line graphs where the price per square foot will dramatically drop down to zero over the periods in time where no sale was recorded. So the line graph ends up looking like a zig-zaggy range of peaks reflecting the price per square foot of a sale, and valleys representing "$0.00" for periods of time where nothing has sold.
To have a Price per square foot of $0.00 is incorrect since in reality nothing was sold.
I would like to be able to eliminate the portions of the graph that have the "$0.00" so that the line graph can "jump" from one sale to the next when the quarterly data indicates 0 units sold.
I'd like to do this without having to eliminate those areas in my spreadsheets. Is there any "catch-all" tool that might do this or is that just crazy talk?
Im trying to capture the first instance of a value(ie 1) and reflect the top row of the value (date). I cant seem to make Hlookup work. I got it to work on the first line using index and match, but when i tried to copy it on the succeeding cells, it gives me the wrong data.
I also tried the following
StartedDate started1-Dec2-Dec3-Dec4-Dec5-Dec6-Dec7-Dec8-Dec9-Dec10-Dec
aaaaaaayes12/1/200911
bbbbbbyes1/0/1900111
b2b2b2b2b2b2no
ccccccccyes111
I have a chart with 8 series of data, 4 of which reflect actuals and 4 of which relates to averages of the 4 actuals of a period of 5 months.
The 4 actuals series are represented by bars on the chart and the 4 averages are represented by lines on the graph.
When viewing the chart it shows 4 bars per period (for each of the actuals) and 4 lines (for each of the averages per period)
How do I get the avergaes points (reflect by lines) to start above the actuals they are averaging? At the moment the points are bang in the middle of the 4 bars in the chart?
Not sure if I have explained my problem preoperly but if not I can email a file showing what I mean if needs be.
I am running a macro to create a combined master summary sheet from data in several other sheets contained in one workbook. That is working fine. I need to create some kind of refresh macro so that when data in these sheets change the master sheet will change and update automatically.
View 9 Replies View Related