I have a workbook with each month as a tab Jan 2014- dec 2014. I have a totals page that's has total billings( the sum is adding all the totals of each month). The totals for each month are in different cells based on the number of individual invoices I enter for each month. I have entered jan- march invoices. I would like to put in a formula on my totals sheet that gives me a ytd avg without changing it. ie: d4/3 then next month april d4/4.
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.
So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
I'd like excel to calculate 3 totals for me based on the colour and value on a worksheet.
Basically, I work for various people and they pay at different rates per hour. I currently have a spreadsheet with their names, times, and rates (see attached for example), but I calculate the amounts paid and due manually. If possible I would now like excel to do it.
To explain further, 'J' gives me $10 per hour, and 'V' gives me $5 per hour. Cells shown in red show work done but not paid for. Cells shown in green show work done and paid for.
I'd like excel to automatically create totals as shown on the spreadsheet, namely: Total due: xxxx Total paid: xxxx Total outstanding: xxxx
At any time during the month I can be asked to take on more work - I would then enter the code into the spreadsheet for the hours requested...and I'd like the totals to be update automatically.
I have a series of numbers in the column AB56 going downwards and in AC56 going downwards their respective counts in a data-set.
What I would like to do is find the average of the min and max, the min and max for the average calculation are both determined by looking at the count column, isolating the max, isolating the min, provided they both have a count greater than zero then averaging both results.
In this calculation the MAX is 0.155326043 because it has a count greater than zero, the MIN would be 0.138120697 as it has a count greater than zero, the average of both would simply be 0.1467. i.e. Average(0.155326043,0.138120697)
I need to calculate the average of values in a particular column if some other column contains a particular text. For e.g. I would like to calculate average age of all the people who belong to the Comapny "XYZ". Which means that I have two columns here one is "Age" and the other one is "Company". So For all the people who belong to the company "XYZ", I need to calculate average age.
I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it. been trying to figure out a macro so when you press the update button it then just updates the monthly total.
I have a list of dates with respective balances. I am wondering how to get Excel to determine if the end of the month has passed after a certain date and then calculate the return for that month. What is the most efficient way in your opinion?
best formula for adding figures in a column that correspond to each month of the year?
I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet.
I am working on a spreadsheet to automatically generate teachers schedules for teaching various students on a weekly basis. (See section of the spreadsheet below)
The teaching dates in column A are generated using the formula
=WORKDAY.INTL(A2,6,11,$J$3:$J$70)
Where Column J is the list of Holiday dates to exclude.
The rate of pay for each lesson is in Column H.
What I am attempting to do is to automatically calculate the teachers pay each month by adding together the range of cells in column H that correspond to the dates taught in a particular month listed in Column A.
I have tried to use SUMIF but as there are a different amount of lessons taught each month the range of cells to add is different each month, I can't see a way to define the cells to add from column H without doing it manually.
Is there a way to define the range of cells to add together from column H based the dates in a particular month?
Date Time Instrument Student Name Location Rate of Payment Holiday2 2012/2013
05/09/2012 14:30 - 15:00 Guitar Student 1 Studio 103 10
If I want to obtain a future value of $500,000 at the end of 20 years, how much do I need to save each year at an interest rate of 10% per annum? I ended up using the following formula: =PMT(10%,20,-PV(10%,20,,-500000,1),,1). Let's define (Insert/ Name/Define) the answer to this function as Pmt1.
By then using =FV(10%,20,Pmt1,,1) to confirm that Pmt1 will end up providing $500,000 after 20 years I get the answer I am looking for but have absolutely NO IDEA why it works. Worse, I do not know whether it is the correct answer. I have the following function (courtesy of someone) that I use to determine the expected future value of a series of annual payments at a fixed interest rate but also with fixed annual increases in the payments. (Example: $1000 per annum is invested for 20 years. The interest earned on the $1000 is 10% per annum. The $1000 increases by 5% each year - i.e. 19 increases)
Assuming the payment does not increase, I simply replace the 5% with 0%. When I run this function and use Pmt1 as the annual payment the answer differs from the one that I get from the PMT function that I quoted above until I change the ",,1" in the function to ",,0". What do I not understand about these functions!? Which is correct or are both provided I learn to know what they do? This is the vaguest question I've ever been able to devise simply because I can see that something is amiss and I do not know what - or how to start figuring out what it is that I "know not"!
I have a spreadsheet with the second tab hyperlinked to the first tab.
Tab 1 Bus DEC '13 NOV '13 OCT '13 SEPT '13 AUG '13
[Code] ......
ERROR: Row 157, Aug '13
Here's the formula I used: =IF('End of Month'!L3>=0,('End of Month'!L3-'End of Month'!N3),IF('End of Month'!L3>0,('End of Month'!L3-'End of Month'!M3),(0)))
Should return value 339.
The formula should simply take the current month value and subtract it from the previous month value BUT if the value is zero, then it should go to the prior month and so on until it retrieves a positive value.
I've also tried to following formula without any success:
=IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!J3), IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!K3), IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!L3), IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!M3), IF('End of Month'!H3=0,('End of Month'!N3-'End of Month'!O3), IF('End of Month'!H3>0,('End of Month'!H3-'End of Month'!I3),(0)))))))
I have US money supply data, arranged monthly from 1975-2008. I need to calculate the monthly growth rates. I would really appreciate some help as I have no clue how to do this.
here is a link to a text version of the data I am using: [url]
I have, every month data like for one Year, every day in a month we will update the sales information in to excel sheets like jan, feb, march, ...December
In the Main sheet i want total, i have bunch of data and wide range of items, how to get monthly consumption into main sheet.
I have a pivot table that summarizes expenses (cash advances, cashe remitted, etc.). The issue that I'm having is the way the data is displayed on my pivot table.
When I adjust the custom calculations to "show data as Running Total in MONTH" I get the desired outcome on my row totals, but I do not get the correct figures on the actual data within the Pivot Table. When I remove this custom calculation and just "sum by value" then the data is correct, but the row totals are not.
In a perfect world I would need the values to sum by value, while the row totals are set to "show data as running total in MONTH". I'm not smart enough to figure out how to produce both.
I have monthly a list of 2,500 people with an allowance value for each individual shown.
Problem I got is that sometimes there is more than one allowance entry for the individual in each month. I just need the total for the individual in that month. Each month is on a separate worksheet. I have been racking my head, but I can’t figure how I can look at the list of people (who have a unique number) and just total it.
I am trying to keep a running total of cost, here is what I am doing
A B C D E F G H I
Date Books Pencils tax Month Books Pencils Tax Total 1/2/2014 11.50 2.50 .76 January 35.85 13.50 3.44 52.79 1/5/2014 14.50 3.85 .83 February 10.95 1.50 .68 13.13 1/6/2014 9.85 7.15 1.85 2/6/2014 10.95 1.50 .68
I only used Jan and Feb as an example. So as you see on the right side I will have all 12 months for the year and I just want all the totals for each month to auto calculate. I will have between 15 and 30 entries each month.
My excel sheet contains a set of date-marked expenditures. It's all one running list - no starting a new column for each month or anything like that.
I'd like to include a separate column/sheet showing total expenditure for each month. I've tried multiple formulae, along the lines of "Sum the B cells but only those for which the corresponding A cell is in the right value range". Or in programming terms, "for i from 1 to infinity, if x < A(i) < y, add B(i) to sum." Since other formulae on the sheet are independent of item ordering (don't require the data to be sorted chronologically), a plus would be if the solution can be, as well.
A separate thing I'd like to do (which I'm guessing has a similar solution) is to include grand (running) totals by expenditure type. A separate column of (non-numerical) expenditure codes is next to the data.
I'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.
I have data in the following format (columns A & B):
Code: Day Value 1/1/2002 2.1 2/1/2002 4.8 3/1/2002 0.1 " " " " " " 31/12/2011 5.2
I'd like to be able to write a formula so that I can get the daily data averaged over each month, e.g so that I have something like:
Code: Day Value Jan 2002 3.3 Feb 2002 4.3 Mar 2002 3.1 " " " " " " Dec 2011 4.2
I'd like the data in monthly averages so that I can graph them. I have read about array formulas and averageif statements, but I can't seem to combine them to make them work.
I need to gather total dollar amounts from a column based on their date/month entered.
I have a sample attached, in cell L2 for example I would need to gather all the dollar amounts in Column G that were entered for that month in column B that correspond to the month in column K.
How can I get a pivot table to calculate the average of the monthly totals and not the average of all the raw data.
Below is a pivot table of the data on the left. When the pivot table averages the months it calculates the average of every single record, 48.83. How can I get it to average the monthly totals:average(apr,may,Jun)= 179.05
Date cost Sum of cost Years Date 4/1/2011 -75.00 2011
2011 Average Monthly Average 4/11/2011 -58.88 Apr May Jun
I would like to keep an average for 12 months.Each number entered for a month would be averaged with the total months with an entry until all 12 months had entries. I am using excel 2007
We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
