I'm trying to calculate the amount of money invoiced per company per month. In column B I have the date the invoice was issued. In column E I have the Company name. In column G I have the amount invoiced.
View 6 RepliesI am creating a membership style database and keep hitting a few walls.
Formula to show money owed;
Example
Date Joined - 01/01/2010
Membership Years - 4
Annual Payment Fee - £25
Paid to Date - £0
Amount Owed - should be yearly fee x amount of unpaid years but is there a formula I can use for this?
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
I was using SUMIFS on a large amount of data, but wasn't getting back the exact sum that I needed.
It turns out that I had cells with values similar to the following
Code:
000123
0123
Basically, text fields with the same numerical values, except for the number of leading zeros. Due to the nature of the table, the number of leading zeros is important.When I did SUMIFS, it counted the sum of the two values together. For example with the following:
Item
Batch
Qty
ABC
123
1
It appears that even if a numeric value is stored as text, SUMIFS treats it like a number.
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies View RelatedI have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
View 3 Replies View RelatedI am trying to create a master spreadsheet to sum up other tabs for the number of funds that are going operational by month. The date is formatted at "January 31,2013".
See attachment as an example : Client Schedule Example.xlsx
Attached is a spreadsheet. The first worksheet is a summary of the data inputted in the second. I am trying to total the "total spend field depending on whether it is "Extra Works", "Spares & Materials" or "Other" which works fine. I would now like to sum depending also on the month that it was spent.
Order Tracker 2.xlsx‎
I am trying to create an equations that sums up a range based on matched dates. I have considered using an array, but that turned out to be much more work than necessary.
I have never used the SUMIFS before and having trouble, here is the equation I'm using.
=SUMIFS($F$2:$F$204238,MONTH(E2:E204238),"=month(referenced_range)")
*F2:F204238 is the range that needs to be summed up
*the month(referenced range) is in date form
*I was able to pull and evaluate all the data on it's own, but when using it in the sumif equation it gave me zero result so it must be a syntax error.
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
There are two tabs in my workbook right now. The first is where users will input information and annual budget number for consulting fees. The second tab spreads the annual budget by the 12 months. I don’t want the users to see or touch this tab containing the spread. Once the annual budget number is entered, I would like them to use a combobox to choice how they would like to spread the budget monthly. One choice is evenly which is the default and the other would be manually. If they choice manually I would like a userform or something where they than input each month’s amount that will total the annual budget. See attachment, this is all I got so far.
View 2 Replies View RelatedI have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
I have a nested if formula that isn't working and I know there is a real simple fix.
Here is the situation: If Company A (A1) and Company B (B1) both have indices greater than 125, I want that to show that in my recommendation. For example: I used if(A1>125&B1>125,"Company A and Company B",if(A1>125,"Company A", if(B1>125,"Company B","None"))). For whatever reason, the first nested if statement isn't working correctly. what I am doing wrong with this if/and statement?
I need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.
Assuming that B1=0 and C1=1 (in the # of days row)
# of Days 0123456789101112131415Total Files
File Count011712113203000000049
The example:
Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.
Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00
Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.
So the sort of result I'm looking for would be like
Assume todays date is 19-04-09
A B C
12-04-09 £5.00 Last Week 4 Payments Value £45.00
12-04-09 £10.00
13-04-09 £10.00
14-04-09 £20.00
I have been looking all over and cannot find an answer on how to do this. I work for a call center that takes inbound technical support calls. We recently added a chat support option for our end users. Our chat agents can take up to 3 chats at the same time. I need to determine the total amount of concurrent chats for each agent per day as well as the total amount of concurrent time for each agent per day. Below is a sample of my data. Notice how the start time on row 5 is earlier than the end time on row 4. I need excel to count this as a concurrent chat and then calculate the amount of time that was concurrent (in this example it would be 16 minutes). Currently I have 2000 rows of data and 30 different agents.
Start_Time
End_Time
Agent Name (Num)
6/3/13 12:13
6/3/13 12:24
14
[Code] .........
I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.
Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine.
I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy.
What I have so far:
p*(1+(r/100))^n
Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan?
Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!)
To keep it short i followed this guide for the amortization table.
and now I am so confused about if I should be using PMT, PPMT, NPER?!
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View 2 Replies View RelatedWe have agreed maximum rates with suppliers for certain services and I'd like to check that the rates they have invoiced fall below the agreed maximum.
I've attached an example of the data I'm working with. What I'm after is a formula that will cross check the details and rate charged on the 'Invoiced' tab against the three 'rate card' tabs and generate an output that flags any discrepancies. I've added a column called 'Validate' on the 'Invoiced' tab where I'd like this formula to go.
All the data should match with the exception of the shift which is listed as a description on the rate card but is a concatenation of the job role and an abbreviation of the shift (D = Days, N = Nights/ Saturday and O = Sunday/ Bank Hols).
I am trying to calculate the total amount of kilos for a specific date with a given time range.
As well as the average time they have been handled with in the same specifications.
Attached is a sample sheet of the info I am working with but I cant seem to get the formulas to work.
Book1.xlsx
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message.
give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount?
attached is the start of the assesment question.
You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid.
Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received:
• If students pay the course fee less than 7 days prior to the course commencing then they receive no discount.
• If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%.
• If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%.
• If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
I need to calculate the subscription amount based on the capacity of the wind mill.
The slab rates are as follows:
1. Up to 0.6 MW - Rs.3,000
2. more than 0.6 up to 3MW-Rs. 7,000
3. 3 MW up to 98MW -Rs. 7,000 for the first three MW and Rs.1,500 per MW in excess of 3 MW
4. Above 98 MW -Rs. 1,50,000
I had tried IF statement but was not successful.
I have 12 different sheets named April-06-sales through to march-07-sales. I need is to be able to search all of the sheets and check if the invoiced paid column is blank and if it is copy that row into a new sheet. All of the sheets have a different number of rows.
View 2 Replies View RelatedI am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.
ie
05/01/07 - 06/02/07 = Jan
06/02/07 - 05/03/07 = Feb
Does anyone have a formula for calculating the amount of money used for a 45 day period.
e.g
01/02/07 $13.00
05/02/07 $22.00
10/03/08 $6.00
etc etc
each amount for a given date added to give a total for 45 days only.
Any easy function to convert numbers into words?
I a looking for an easy function to transform numbers into words as shown :
Value------Output :
12624-----Rs Twelve thousand six hundred and twenty four only
12624.24--Rs Twelve thousand six hundred and twenty four and twenty four paise.
Hopefully the currency should be changeable ( dynamic) i.e Rupees to Dollars..
In India , we also use lacs and crores , so something in those lines if possible...
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.
View 4 Replies View RelatedI am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.
Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.
Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?
IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".
The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).