Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total
Mar 6, 2013
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] ...........
View 1 Replies
ADVERTISEMENT
Jul 10, 2014
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.
writing a formula to evaluate over 360 periods.
View 5 Replies
View Related
Jan 7, 2014
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
View 2 Replies
View Related
Feb 13, 2014
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
View 6 Replies
View Related
Feb 28, 2008
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]
View 9 Replies
View Related
Aug 31, 2006
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.
View 9 Replies
View Related
Feb 8, 2010
I need to produce a summary of the monthly spend with parts suppliers for a number of vehicles, with each vehicle having it's own worksheet. The suppliers' names are in column C with the costs in column H.
So what I need to do is sum the results of column H on each worksheet when the supplier's name (which is on the same row) matches "Triple 7" and it's in the same month. The idea is to produce a sheet which displays the total spend each month with our suppliers.
View 9 Replies
View Related
Mar 26, 2014
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.
View 1 Replies
View Related
Jun 17, 2014
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.
View 5 Replies
View Related
Mar 16, 2009
Im trying to work out the formulae or fuction that will work out the monthly compound rate of a loan.
The loan details are £140,000 at 7.55% APR for 20 years.
View 2 Replies
View Related
Aug 3, 2009
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:....
View 9 Replies
View Related
Jun 13, 2014
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.
Colac Production.xls
View 7 Replies
View Related
Aug 14, 2014
Find the attached excel sheet.
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.
excel.xls.xlsx‎
View 1 Replies
View Related
Dec 5, 2008
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.
View 8 Replies
View Related
Jun 4, 2007
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
r = 100((Y/X)^(1/n))-1)
So for X = 200, Y = 750, n = 10, we have
r = 100((750/200)^(0.1))-1) = 14.1309%
View 3 Replies
View Related
Jan 26, 2014
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.
View 5 Replies
View Related
Jan 25, 2014
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.
View 2 Replies
View Related
Mar 3, 2014
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.
View 3 Replies
View Related
Nov 3, 2009
I want to create a summary pivot table report + chart. What I have is 12 worksheets, one for each month, each with total amount invoiced and gross profit by client.
I want a pivot table that shows me the clients and amounts each month (this is the bit stumping me). Basically I want a drop down box to select the month on the pivot table, which then shows the data from the relevant worksheet (Aug-09, Sep-09 or whatever).
I've tried using multiple consolidation ranges but this just aggregates all the data; it doesn't allow me to limit by the worksheet the data has come from.
I'm wondering if I have to consolidate all my months into one big sheet, then add a field for the month. I would rather avoid this but accept it might be the only way.
Final thing; can't use VBA at all (too much hassle for IT apparently).
View 2 Replies
View Related
Sep 22, 2011
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] ........
View 1 Replies
View Related
May 20, 2008
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?
View 9 Replies
View Related
Dec 27, 2013
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)))))))
View 7 Replies
View Related
Dec 6, 2011
I am improving an old spreadsheet that contains a tab for inputting the following data, all using data validation:
date
employee (who did the work)
team (for whom work was done)
department (for whom work was done)
type (of work done)
Project (which property worked on)
new/revision (was this a new project or revision of old)
time (spent working on this)
I have a sheet with a drop down in which you can sort by employee and the associated chart and graphs adjust the information accordingly.
I need to create a chart and graph in which I can track the trailing month as well as year to date. Ideally, I would like to sort by the following:
- This month (i.e., the 1st of the month to present)
- previous month (etc., be able to select all previous months for which data was entered, i.e. Jan., Feb. March. etc.)
- Year to date
I know there is a way to do this - I thought maybe it would be through some formula incorporating =today() and subtracting back to what you need using numeric dates?
View 1 Replies
View Related
Sep 23, 2009
I have a couple Sumifs formulas that I am trying to return monthly values for but there are small discrepancies on the values that they return. When I manually select the cells that I'm targeting with the formulas, I get a different value. I have checked the formulas 100x & can't figure out what is wrong with them.
Example:..............
View 2 Replies
View Related
Oct 17, 2011
I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.
I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.
Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?
View 1 Replies
View Related
Oct 24, 2013
I have a data series running horizontally, which gives me monthly values e.g.
Jan 10 - Feb 12 - Mar 11 etc. Imagine the months are in row 2 beginning from column 2, and the values are in column 3.
I would like a formula which adds the three values, and in row 6 column two gives me the total for the quarter (10 + 12 + 11) and displays 33.
The next quarter value, I would like in row 6 column 3, etc.
View 5 Replies
View Related
Jan 23, 2014
I have a series of 12 monthly data values:
1/31/19990.155375
2/28/19990.179262
3/31/19990.179392
4/30/19990.178993
5/31/19990.17128
6/30/19990.139678
7/31/19990.15518
8/31/19990.15536
9/30/19990.17732
10/31/19990.117725
11/30/19990.134171
12/31/19990.149113
and also a series of the 365 days that comprise these 12 months.
I need to populate each of the 365 days within this period (1/1/1999-12/31/1999) with its corresponding value for the month, i.e. 1/21/1999 (and all days in Jan) will take 0.155375 and 5/4/1999 (and all days in May) will take 0.17128.
How I should set out my data and which formulas I need to use?
View 8 Replies
View Related
Jun 9, 2014
How to calculate the monthly sales based on the data given ?
View 4 Replies
View Related
Mar 4, 2014
What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).
View 6 Replies
View Related
Jun 3, 2006
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.
View 14 Replies
View Related
