I have a sheet with 3 columns. First one is a date in the format dd/mm/yy, second is category type (numerical 1-40) and then the final column is cost in the format 0.00. These columns will need to run from A2:A65536, B2:B65536 & C2:C65536 to cover all later additions. I need to work out a cost total for each of the categories in each month.
View 5 RepliesI am trying to create vba macro that allows me to calculate cost per month.
I have created a basic table with two columns
Cost
Month
200
Jan
300
March
150
Jan
470
April
150
March
I have another sheet , that has two columns
Month - Cost
Jan
Feb
March
April
I am trying to sum up the value on sheet1 and input into sheet2 on the allocated month.
So far logic , that I have came up with is.
I create a For loop statement that looks column and sort each month into an array.
Than the second loop will use the array to find the cost with the allocated array value to sum it.
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.
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.
i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago.
I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information.
For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month.
Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month.
Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.
I am a Supply Chain intern at a company for the summer. One project I have been given is to create a tool in excel that allows our purchasing people to type in certain inputs (origin, destination, etc.) to calculate the total cost of shipping an item from a supplier in anywhere from china, to somewhere locally. I am gathering quotes for shipping via ocean and truck. How can I get where I enter in one location, say shanghai, china, shipping to another location, say Norfolk, VA, and get the total mileage difference to show?
View 1 Replies View RelatedI need to calculate the total cost of outbound calls based on the total duration of outbound calls multiplied by cost per minute. For example, in a given month, the total duration of outbound calls is 261:16:34 being 216 hours, 16 minutes and 34 seconds. I have this figure in cell A1 with the format [h]:mm:ss. I then convert this to minutes in cell B1 by saying B1=A1, but having the format [m], which gives me 15676. In cell C1, I have the cost per minte value of £0.026. But when I apply the formula D1=B1*C1, I get £0.283, when 15676*£0.026 should in fact be £407.58.
View 2 Replies View RelatedI am having trouble trying to calculate cost for a specific task. I know this is something simple and I am going to kick myself when it gets solved, but I have total brain lock right now! Here is the example of what I am trying to do.
A B C D E F
# of people start finish time man hours labor cost
3 1:35 2:05 :30 1.5 $15.00
I am entering the values in A, B and C, with B & C formatted as TIME. D is calculated by =(C3-B3), but I am lost trying to calculate E and F.
I'm working on a problem that calculates data using a binomial distribution. The data derived from the binomial distribution is then used to calculate a cost. I would like to minimize cost by changing the number of " reservations". Can excel solver do this or is it too complicated? I have attached the file with what I'm working on. (Changing E1 to minimize E2 while Cells A9:A102 are calculating a binomial distribution)
View 7 Replies View RelatedIn attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies View Relatedis there a formula I could use to total for each month all of the e.g. C3303, for each of these categories as detailed in the table at the bottom
View 4 Replies View Relatedi have attached the spreadsheet
basically I am trying to total this spreadsheet up to work out a sum for the month by customer number. some customers made a payment in jan 2012 and paid again in jan 2013, some paid twice in one month.
what needs to happen is
it needs to have customer number going down and the month/year going across the top with a summary paid for that month
i have attached the spreadsheet.
Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
I need to calcuate how many times a date( any date for the month) aprears in the list. it needs to be calucated by each indivual month form a list that will span over a 12month period.
the problem i'm having is how do i get it to only read the month that i want?
I have to calculate the flat revenue split by month between 2 different dates. The two dates may be in the same year or different years.
excel formula for the same.
Is it possible to do the same in pivot?
In cell A1 I have a date 1/1/14
I need to get the number of business days for the month that is entered in the cell. I tried NETWORKDAY formula but needs a start and end date.
I am looking for a formula that counts from each particular month
The main List has the following
Column C has all dates
Column D has hours worked
Column E has engineers name
The Second List is broke down into months and hours
Column I Has month ie I4=Jan 14, I5 = Feb 14 etc
Column J I need to have a total taken from column D from the month in column I
I trying to find a formula to give the difference between two dates in year and month.
For example, the start date is Feb 1 2006 and end date is Jan 1 2008.
The formula should result in 2.11
I tried the following formula from a previous thread but it gives the answer in text but I need it to result in 2.11.
[url]
I've managed to sort the first half of this issue, in that I can calculate the number of days within a given month which fall inside specified to and from date parameters..
The formula I've used is thus :
Code:
=IF(EOMONTH(A13,0)EOMONTH($I$9,0),0,IF(EOMONTH(A13,0)>$I$9,$I$9-(EOMONTH($I$9,-1)),EOMONTH(A13,0)-(EOMONTH(A13,-1))))))
This examines a date value (A13 in this case) and assess whether any days within that month are between the two date parameters specified in H9 and I9.
H9 = 01/01/2011
I9 = 15/05/2011
Jan-11
31
[Code]....
What I need to do now is specify a second set of date parameters in H10 and I10 and add those into the assessment. The dates will NOT overlap.
So, for example, if I were to set H10 as 01/07/2011 and I10 as 15/07/2011, the results would appear as below
Jan-11
31
Feb-11
28
[Code].....
I need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.
I have three columns; Date, Company name and Payment mode.
Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234
At the end of each month, my agency collects data about clients served in our programs. One of the many pieces of information they want is the number of "bed days" a client was residing within our programs for the given month.
For example:
Jon Doe entered the program on 7/16/13 and discharged on 8/15/13. I would like to know how to calculate the number of days in July that Mr. Doe was residing in the program. (The date of discharge is not included.)
I recently took over the responsibility of completing reports such as this one at my office. The previous person who completed the monthly reports would count the days by hand for each client, but I know there must be a better way.
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
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 m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
Basically what i need to do is create a validation list, the contents of which will be dependant on the entry in another cell. The catch is that the other cell contains an "if" formula.
eg.
A1: 10
B1: =if(A1=10;"A";if(A1=11;"B";if(A1=12;"C";"Invalid Entry"))) ....
I want to sum a range of cells but exclude any that are in a particular colour (in this case red).
View 3 Replies View Relatedin cell d1 i have
hk/q46559
in cell i1 i need to return whatever is before the "/"
in this case hk
and in cell i2 i need to return the number after the q
in this case 46559
on sheet1 which is called working sheet and cell d25 is asking what type of goods been sold
i have several different sorts"
ie
s25
s28
s50
s69
s70
s82
abd
t70
t140
i want the spreadsheet to look up somewhere on a sheet called delivery all the components that could be used for that type
so i guess i need to name define?
ie if s25 is selected
on delivery sheet in a20 it would then list all the components underneath each other.
I have a worksheet with 4 drop boxes that have the following options in order: 2,28,10,24. My main concern is to make the last drop box depend on the third: so the one with 10 listings will determine which of the 24 will appear according to selection. The other two are not necessarily needed to be dependent although, they will be pulling different data according to the selections. How to make the last two be dependent? The combo box selections will determine the values pulled for four different time frames.
View 4 Replies View RelatedI'm creating a spreadsheet to track orders as part of a project, the spreadsheet currently lists all the different options that can be ordered, it then lists all parts of the order, installation & setup etc.
However it is likely that not all of the options will be used.
What I would like to do is create a few drop down boxes for each option, if 'yes' is selected then the order tracking for that option shows below.
Example, the wants 'internet' so 'yes' is selected for internet using the drop down at the top of the page. Further down the page all the order tracking info is shown for internet. If 'no' is selected nothing is shown for interent.