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.
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 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 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
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?
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 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 am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank...
What I am trying to do is:
Cell A1 is my cash budget Cell A3 is actual cash used Cell A5 is The Difference between A1 & A3 (sum A1-A3)
What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..
For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required". If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".
I am trying to write a formula for my account statement its got a list of dates of invoices descriptions then the value of invoice.
I want to check the dates of invoice to the date of statement if less the 1 day invoice total to go in a box called current, if between 2 and 30 days to invoice value to go into 1 to 30 days past due, if between 31 and 60 days invoice values to go into a box called 31 to 60 days past due, and then if dates are between 61 days or more then invoice totals go in to a over 61 days over due box.
I have to calculate the totals for every individual for the past three months. I have to do this for 200 people, is there a formula i could use to do this? Eg. if a person made 50 sales in Jan, 40 sales in Feb and 100 sales in March i need a formula that calculates the grand total, which will be 190.
I need to find a formula which will give me a total of £'s between a start and end date.
MY data is an extract with names, individual dates (Ie, 01/05/08, 02/05/08, etc) and costs per day. What i want to do is show a total for the month using the start and end date of that month to add up all the costs within.
I believe there is a way to summarize monthly data with the month in rows and the heading across columns. The goal is to have a formula summarize the data with Q1 through Q4 in rows and headings across columns and vice versa. Sample data attached.
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.
If i borrow Rs.50000/- at 7.25% interest compounding monthly, repayable in 4 quarterly instalments, when i put it in excel using pmt function for emi quarterly payment and ipmt for interest calculation at the end of the 4th quarter (i.e last instalment) the balance will not become zero it shows a balance of Rs.14.35 its due to interest compounding monthly, is there any formula in excel to overcome it?
I'm trying to make a worksheet that calculates monthly residuals. For example, if I have income of $275 in the first month and then the next month I have the first $275 and an additional $275, and so on. I've created the following formula
I need is a formula that averages totals from different months out of the year. I already have a yearly average. That was easy. But what I need is a 3 & 6 month average.
I also need it to be most current, so when I am in September, it will take the 3 previous months and average them and same with the 6 month. Then when I move into October, it would take its 3 previous months, i.e. - July,August, September.
I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.
I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.
This is basically what i'm trying to do: If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?
I have an issue whereby I have thousands of lines of data. I need to bring back the "previous month" worth of data (along with some additional information) but not sure how to go about this.
The attached shows the example file. I need the following : 1. Take the data to populate the "Populate" tab 2. Using Column F (Invoice Date Created) to only bring back the "previous month data" - in this case it would be May 2014 3. The following formula is what I have currently for Column B of the "Populate" tab: =SUMPRODUCT(SUMIFS('Data'!$N:$N, 'Data'!$M:$M, "Invoice Payment Processed", 'Data'!$A:$A, A2)) 4. The above formula brings back all the values - I only want the previous month. 5. I am then not sure how to calculate Column C - which is a count of the amount of invoices that make up the total
I am trying to get the formulas to work on Sheet 3, the rows formated to percentages correctly, but they are not reading the totals in row 2 to make the formula complete. I also want this to run more than one number, I would prefer a range for example value >= 1.29 so that Sheet 3 shows all items less than 1.29.
I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.
If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match 2) In column "G", sum up all the values in "F" that go with the matching values in column "A" 3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.
I have lots of monthly reports to prepare. So what i do is to copy previous month report file and paste and rename it as current month report. Once i open it, i need to change the linking to the next colume of the source files. Eg Source files July is in Column H, then now i have to change it to Column I in my current month reports.
Furthermore, one file may have a few source files so the column may differ also.