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 need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.
I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.
I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.
Attached is my example.
Only recently have I really stumbled upon some of the more advanced functionality of excel, and I was literally blown away. I'm now trying to learn more and more about it.
[URL]
I need the Monthly totals of Apples, Oranges, Apples & Oranges.
I know how to select it by hand with the:
=SUM(, hold shift, mouse select the apples for March, and do the same for the other columns.
But how do you do it via a formula? Also, is it possible to make it dynamic? i.e. you add more transaction dates later, and the whole spread sheet gets updated automatically.
The only way I see how to do that is to write a loop that goes through all of the records. How would such a loop look like? Or is there a better way?
I've got data in E4:I4, Monday - Friday.
What I need to do, is add the total of those 5 figures in a total, but then clear the data in those 5 days.
Then when I input the data for the next week in to those days, I want it to add to the previos week.
So basically;
In week 1 the 'Total' is just week 1
In week 2, the 'Total' is week 1 & week 2'
I will only be going up to week 5. (only week 5 once in a 3 month period, so 2 4 weeks and a 5 week)
Spreadsheet is made up of Col A - Col AK:
Col A = Purchase Date
The info is continuous:
Row1 12/1/06
Row2 1/2/06
Row3 3/5/07
I need monthly totals to display simultaneously. Do not want to use Subtotals option.
I have a daily rainfall data and I want to convert it to monthly totals. I tried the pivotal table but the arrangement of the data seemed to be confusing (i.e the year, month and day are in different columns).
See the attached data: Raindata_excel_forum.xlsx‎
I assume there's a database (or pivot table?) solution for my task, which is to detect the days, weeks and months within a very long table, and obtain the totals of the data therein.
See attached worksheet.
I need to make a sheet that give totals based on monthly figures from last year.
Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.
One month Last year the store made 31.82% on it’s money.
The bonus for anything over is $75 per 10% increments.
Ie
0.00% $400
0.10% $475
0.20% $550
0.30% $625
0.40% $700
So this month was over last months, 37.18% so the bonus was $3,975.
What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%.
This hurt my head trying to get it right and im new to this more complicated formulas.
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
[Code] ........
I have an expense ledger that looks essentially like this but spans seven months and counting:
HTML 18-Apr-08pizza$10.00Food
14-Apr-08book$12.50School
13-Apr-08milk $3.99Food
13-Apr-08soap $3.99Grooming
I have been using pivot tables with great success to summarize my data according to categories, payment methods, etc. But now that I want to track monthly spending for each category, it is not working out. Grouping the date fields doesn't work for me for the following reasons:
1. After grouping, the pivot table treats Jan as the oldest month for sorting purposes even though it isn't so in my data set.
2. Based on #1, I believe that the pivot table’s monthly grouping works like the month() function, which returns the month without the year attached. This will not work for my data, which will likely span more than a year eventually.
3. When I choose to group the date fields in one pivot table, they also get grouped in other tables where such grouping is undesirable.
Can I use sumif() or sumproduct() in conjunction with a pivot table or some other way to tabulate my monthly totals?
I am trying to do an automatic input with my daily total sales.. i did it in total but my problem is i don't know how to make the date change.
View 2 Replies View RelatedIn column A I have dates, in column B I have data.
What I am trying to do is get the monthly totals from the data so instead of:
01/02/08 - 52
06/02/08 - 87
14/03/08 - 23
12/13/08 - 12
I would get:
Feb 08 - 129
Mar 08 - 33
I have the following setup:
52 weeks of 2010 cost with the following structure:
week number | wk1 | wk2 | wk3
week ending | 1/10/2010 | 1/17/2010 | 1/24/2010
weekly cost | 57,000 | 35,000 | 42,000
I want to be able to use the above data to present a summary, but instead of weekly, list the data on a monthly basis:
month | Jan | Feb | Mar
monthly cost | wk1-4 | wk5-8 | wk9-12+3days wk13
Any way to set this up automatically instead of having to split the weekly data as many months share the same weeks?
see the attached workbook with two different sheets (same data) using different formulas. Each has problems (red text) preventing me from moving forward.
I need to calculate time in level 1, time in level 2 and total time for each row and then be able to sum for the month. Unfortunately, sometimes there is missing data, but I still need to calculate everything possible using a consistent formula that can be applied to the column/row universally.
I am attempting to build a spreadsheet for work, and I'm having trouble calculating a correct total.
In essence, the bit of my spreadsheet I'm concentrating on is 2 columns:
Column A is headed "Type of Work" and Column B is headed "Completed".
The Type of Work will either be Letters or Memos, and the Completed will either be Yes or left blank to indicate No. For other reasons, No has to be represented by a blank cell.
At the moment, I have 12 rows, going from A2 to B13, with data such as the following:
Letters Yes
Letters Yes
Letters
Letters
Letters Yes
Letters
Letters Yes
Memos
Memos Yes
Memos
Memos
Memos Yes
I need a summary section at the side with the following calculations:
Total Number of Entries
Total Number Outstanding
Total Number of Letters Outstanding
Total Number of Memos Outstanding
However, as I have only populated it with 12 rows of data for my testing purposes, I need to build formulas to take into account the fact that a maximum of 2000 rows may be filled in by other people over the next few months. Therefore, my formulas look like the following:
Total Number of Entries (stored in E2):
=COUNTA(B3:B2000)
Total Number Outstanding (stored in F2):
=E2-(COUNTIF(C3:C2000,"Yes"))
Both of the above formulas work perfectly, and when I enter a 13th and 14th row, the totals update as I want them to, so I have no problems there.
However, I am struggling to enter a correct formula which will calculate the total number of Letters that aren't Completed.
I did set some names and tried to use:
{=SUM((Type="Letters")*(Completed="Yes"))}
but it gave me a #NA error, presumably because the named range was looking at all the empty cells up to A2000.
I have an Incomes & Expenditures spreadsheets. When I add an expenditure to the sheet I can choose which category of expense it is from a drop down list of expenses. At the lower part of the sheet away from main section I have a list of the categories which I would like to have monthly totals alongside. with the formula?
View 7 Replies View RelatedI have been using this formula and I need to update with new changes.
Code:
=IF(A13="total",COUNTIFS(A$13:A$144,"total",J$13:J$144,"
I attach a couple of files that i receive a few times a year and wish to be able to count the numbers in a more effective fashion than i have attached. Essentially, the source data file naturally changes its numbers each time i get the file so the file that i tinkered about with (also attached) would need to have each and every formula moved to tally correctly. This seems to be a waste of time and would gladly appreciate any assistance. Columns A & C are irrelevant for the purposes of the exercise and can be ignored.
I have to be able to count the data according to employee number groups:
0-19 employees
20-49
50-199
200-499
500-25000
While I have already done this on the attached file you will be able to see that its a long drawn out process. Can anyone advise a better way?
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?
Here is a small data sample:
27/12/2005 06:23 0.3%
27/12/2005 05:47 -0.6%
29/12/2005 06:53 1.3%
04/01/2006 17:55 -0.1%
09/01/2006 15:35 3.99%
09/01/2006 15:46 2.54%
09/01/2006 17:07 1.8%
12/01/2006 07:12 -2%
12/01/2006 13:37 1.5%
12/01/2006 13:39 0.8%
12/01/2006 13:58 0.01%
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
[Code] .........
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)
=Pmt1* SUMPRODUCT((1+5%)^(ROW(OFFSET($A$1,0,0,20,1))-1),(1+10%)^(20-ROW(OFFSET($A$1,0,0,20,1))+1))
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 a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?
View 1 Replies View RelatedI 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.
View 1 Replies View RelatedI am trying to accomplish is to associate each product on the Distribution tab in 'E' to its associated quantity in 'F' so I can count the total number of each product and display this in 'D' on the Totals tab. Once this is done, the total for each product would me multiplied by the associated price in 'C' on the Totals tab to achieve the total item value for each item in 'E'. I have included the current results and what the correct values should be. As you can see, I tried to use 'Countif' with no success. I could not find any solutions in the forum that were close enough to this situation to work.
View 4 Replies View RelatedI have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
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.
Link for excel file is here: [URL] ...........
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
Here's what I have...
Column A Column G
date $ Amount
my data example...
10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/8/2008 $8.00
10/8/2008 $25.00
10/9/2008 $75.00
What I want the formula to do is look in Column A find all of the dates that are on the same day and then look in column G and add all of those $ amounts.
So the result would be...
10/5/2008 $30.00
10/8/2008 $45.00
10/9/2008 $75.00