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'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 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 RelatedI 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")................
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.
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 RelatedSo 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.
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
im running a new bar in Leeds and im trying to create a spreadsheet which will tell me how much to charge per drink to gain a 80% GP on products, ive got as far as listing the products and prices and calculating how much every measure (25ml) will cost. In the past ive used formulas where I can input the price i charge and it will calculate the GP (=SUM(D10/E10)*100-100) for example but id like to be able to type in the GP I want and for it to tell me the price i need, im not sure if tis possible but it will be a massive time saver.
View 9 Replies View RelatedI have some activities that needs to be done ina specified time
Like for example
TAT HRShrsACT 14ACT 28ACT 324ACT 42ACT 54
And i have a sheet where in i need to calculate turn around time
ActivityStart timeEnd timeTATACT 1ACT 2ACT 3ACT 4ACT 5
Based on time entered in start time and end time the TAT column should calculate the TAT based on the above standards.
I also would liuke to exclude weekends(Saturday, sunday). Also the TAT should only be calculated during working hrs between 7:30 PM to 4:30 AM.
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.
Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.
To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.
I am on an auto show board and want to come up with a way of calculating the ballots that come in, using excel. Ballot points are accrued as follows: 5 points for 1st place, 3 points for 2nd place, 1 point for 3rd place. With about 25 different categories (classes) and I am estimating participants at around 100.where I can in the automotive field knowing there is nothing besides personal gain.
I am having a problem with some of the cells in a spreadsheet not calculating.
For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).
I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?
For my Intro to Engineering class, I collected data of two filter media in the laboratory. The data included the % of the media which passed through a sieve and the sieve openings. For use in a certain equation, I need the opening size at the 10th, 30th, 50th, 70th and 90th percentiles. I've tried searching on the Microsoft website but I don't think the PERCENTILE function will work correctly with the given data. I also tried using the slope between points to find the opening sizes at the percentiles mentioned, but doing so yielded different results from my professor's. Attached is the data I collected organized in tables and graphs of the data. I have also attached a pdf file of an example which my professor has given us.
View 5 Replies View RelatedI think a really easy no brianer, I need to find the difference in percentage terms of two values. I am using =(B2-A2)/ABS(A2)
where B2 = 24.09
and A2 = 21.08
Problem - billing spreadsheet for prisoner fee.
1 - 8 hrs = $55
9 - 24 hrs = $55 + $65 or $120
Anything over 24 hrs - $65 for each additional (24 hrs) ($185)
So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.
In order to compute and forecast an estimate of the triennial rent adjustment, i need to compute the rise in the cpi (table) from the lease date (cell c3) and the present date (cell a2) or the closest publishe date existing in the table. The % rise is easy to calculate present index / opening index -1 = % rise in cpi. I update the index monthly when onthe bls publish it.
View 3 Replies View RelatedI input a month as a start date, and then want the next x number of cells to increment by one month based on the start date. I thought this would be easy using the preset DATE function but I can't get the thing to do what I want.
View 9 Replies View RelatedFirstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.
View 14 Replies View RelatedI had an excellent response last time I posted here, this time I’m stuck again with a new formula. I’m trying to calculated amounts between different times, but keep tying my self in knots with complicated IF formulas.
Is there an easier way to work out hours worked between 2 times, but too complicated things further I need three separate amounts so I’m guessing I’ll need three separate formulas
Hours between 00:00 – 06:00
Hours between 06:00 – 19:00
& hours between 19:00 – 00:00
An example could be, 05:00 – 20:00 should be 1,13,1
I have run into a problem I have not found a solution for surprisingly. I need to calculate several fields on the form prior to saving it and thus exporting the data to the worksheet. All I can find is how to calculate the field in a worksheet and then reference it with VB code on the form. Is what I want to do possible?
If I run into many more problems I may want to shop this project out as my schedule barely allows for the work I have let alone attempting to learn VBA on the fly.
If anyone is interested I can post the project so far along with the detail of what I want it do in the end.
I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it.
Is there a way that I can speed this process up, using some code when I run certain sections?
There is a copy of what I am doing, so you can see the amount of formula.