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 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 need an formula to calculate employee vacation packages. The requirements are:
- 12 months of service = 40 hours vacation
- 24 months of service = 80 hours vacation
- 60 months of service = 120 hours vacation
- 180 months of service = 160 hours vacation
- 240 months of service = 200 hours vacation
I have the employees 2014 anniversary date but I cannot figure out how to sturcture my IF formula. So basically, if an employee was hired 1/8/06, I need the formula to spit out the appropriate vacation hour balance based on the chart above, which would be 120 hours because eight years of service falls between 60 months and 180 months.
I 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 have a form for calculating costs of products ordered which imports quantity and descriptions from another sheet. The imported info and basic calculation of appropriate cost based on ordered quantity seems to work okay. These formulas are as follows:
First column: =IF(ORDER!A5>0, ORDER!A5, "") inserts the quantity ordered
Second Column: =IF(ORDER!A5>0, "Part Description and Part Number", "") inserts the product description
Both those formulas return a blank cell if nothing is ordered. Then using an outlying cell (G3) I enter the following formula to determine appropriate cost based on total order quantity e.g. $2.57ea if total ordered quantity is less than 50 and $2.37 if total quantity is greater than 50.. {Q: How do I put a third pricing level in here??}
=IF(SUM(A5:A27)>50,"$2.57","$2.37")................
A4 is Date Assigned (MM/DD/YY) on the Activity sheet
S4 is the month assigned which I extracted from A1 (January, February, etc.)
J4 is the name of the processor
On a separate sheet I'm trying to count how many packages each processor does per month. On this sheet I've entered (as an array) =SUM(IF(Activity!$J$4:$J$390="ProcessorA",IF(Activity!$S$4:$S$390=$A$4,1,0))).
This doesn't work. However, if I delete the formula and formatting from S4 and manually type in the name of the month in each cell in S column it does work. So, how should I be formatting Column S and the the month column on the other worksheet so that the formula will work? I'm using Excel 2000 and have attached a mini sample.
Is it possible to update a cell with an additional number eg from 22 to 23 when a template is opened. In accounting packages for example, every time you open an invoice it automatically increases the invoice number to the next higher number. I was thinking I might be able use excel to create an inwards goods receipt for items received for repair.
View 9 Replies View RelatedI have a list of part numbers which repeat when there are multple prices they were sold at. For each unique part number I need one median price. The list if of about 500 parts but with the various different prices the file is 3700 rows. How can i do this quickly? For each unique part number I need one median price.
View 3 Replies View RelatedI'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.
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:.......
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/????
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 Replies View RelatedI 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've attached a very small spreadsheet where a few cells are highlighted.
These cells are where I can't figure out how to perform the appropriate calculations.
None of the cells can ever be moved. They are linked to AutoCad and MS Access.
I've calculated the few I know how to do, but when there are two types of criteria, I don't know how to set up the formulas.
I have a problem with using VBA to calculate max/min within multiple selected ranges.
Here is the file: [URL].... I've also attached it below the post.
Column A to D contain the raw data, column G to L contain the trading data. Each trade is marked with "tick" which consists one buy/sell and one close. The entry and close date&time are also included. Then how to match each entry and close date&time from right to left and therefore to look up max/min value within entry and close time from the raw data in the left columns? Respectively, I would like to calculate the min(low) for a buy/close tick and max(high) for a sell/close tick.
The challenges for me:
1.How to match, or reference from the right to left. I knew that "vlookup" could only match one certain value. (correct me if I didn't know enough about "vlookup")
2.The date&time in the left are time intervals while those in the right are time points. How to refer and locate them?
3.In the right side, length of intervals that each tick marked (i.e.from buy to close) are not the same, so should I use a array to contain the length, and then calculate max/min within each? When the data amount get larger, it is not possible to manually use "min" function.
example2.xlsx
OK I have included the spreadsheet I am working with and I will try really hard to explain exactly what i want and where my problems lie, so please bear with me.
The work book is separated by months. The individual spreadsheets can have multiple entries in each cell, I have a code running to automatically pop up a comment text box for each entry into that cell to separate each occurrence.
Now I have a separate sheet that will be showing how many times each item has a "hit" or occurrence for the entire month. That's where I have the problem. I don't want to go and count every time i made an entry, excel should do that. The problem I am coming up with is that I can only get it to calculate the one occurrence per cell which does me no good if there were three separate occurrences in that cell.
So I can easily put together a formula that maintains a consistent markup price across varying costs. Is there a formula that will give me a consistent gross margin % as I drag down a series of costs?
So if the cost is 158 a 42% mark up would be 224.36 but the Gross Margin % is only 29.58. I want to be able to hit a 42% mark up across the board.
Is there a way to accurately compute a conversion report that has agents with multiple roles? Right now it is done this way:
Agents are skilled to two skills, sales & service. They will make most of the bookings on sales (few bookings on service). I want a report that will calculate the agents who are on sales/service bookings vs calls while persons on service/sales bookings vs calls.
Right now, because the service/sales agents get way less sales calls, you will find that their overal conversion is way higher than an agent who is a sales/service agent. What this does is drive the overall conversion rate so high that it always favour the agents on service/sales, a sales/service agent is always disadvantaged.
Is there a way in excel where i can more accurately calculate this while maintaining some degree of parity?
See report attached
I have highlighted some of the SE/SA agents that are way high
Note: SE/SA = Service/Sales SA/SE = Sales/Service
What I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected
My excel data is set up like this
(A).....................(b)......................(c)..................(d)........................(e).........................(F)
.......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg
1. City
2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50
3. Winnipeg....................................$3.20..................$3.75..................$3.6..................$3.21
4. Saskatoon..................................$5.4..................$5.2..................$4.90....................$4.70
(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)
I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:
..................A............................ B..............................C.
..................Weight..................City..................Charge per 100kg
24...............800..................Toronto..................$5.50
I have a sample of aproxomatly 30,000 records, and I am working to do some data analysis on it.
I am comparing multiple fields using sumproduct(), but if I go above two criteria, the calculation time becomes incredibly long. Is there a faster way to preform these large comparisons, or am I stuck watching my Excel lag out for 5 min every time I recalculate the sheet?
how to calculate time overlaps between two ranges using a MIN/MAX formula. Now I need to do the same, but for three (or more) ranges. I have searched and searched, but I can't find a solution. I've attached a sample spreadsheet.
In the example, John is working three jobs, job one from 8AM-4PM, job two from 12PM-2PM and job three from 9AM-11AM. There is a total of 4 hours of overlap, 2 hours from job two and 2 hours from job three. How can I get Excel to calculate that for me?
I'm trying to figure out whether or not something is possible in power pivot. I'd like to load two tables into powerpivot and then set up formulas that create calculated values across the two tables.
Here's a simplified description of what the tables look like. [URL] .........
The pivot table should join the two tables on Month and Category and allow me to create calculated values across the cost category, like this. Note it's filtered to a single cost category right now: [URL] .......
In this view it's filtered to all cost categories but i'd be looking to still maintains the right count of members (i.e. it doesn't inflate the numbers from duplicate records as you might get if you were to join the two tables in a sql database): [URL] ....
The actual tables I would use are quite a bit larger (too large for excel without pp), and have quite a few more of each type of category.
Would something like this be possible to do in powerpivot without having to do some data prep work in another app to join the two tables together?
I'm using Excel 2003, and successfully working out start date (T2 in the formula below) plus duration in days (U2) minus any non-working days listed in a seperate worksheet. =WORKDAY(T2,U2,Holidays!C6:C17)
Each 'task' is on a seperate row, and the lead officer is named in Column E. How can I get the work day function to include the non-working days for each officer as well as the global non-working days in the seperate worksheet? I thought about using a vlookup, but that only matches the first non-working day for each officer rather than all of the non-working days.
I have data in Columns A to P
Column B contains specific groups.
I need to show the following:
For each group what is the count in column P that is