Increasing Value In Spreadsheet By Set Amount Monthly?

Jul 31, 2014

I need a series of cells to increase by varying amounts after each month, however this needs to happen automatically so if I send the spreadsheet to someone else, it will update for them automatically. I'm hoping that I can set up a function that will ONLY increase the cells after 4 weeks after they select a date from ANOTHER drop down menu (i.e they select the starting date from cell A1 - then input their starting numbers into other cells which then increase on that same date, 4 weeks later).

The cells that need to increase already have a formula in them as well; as this number is their predicted 1RM based off formulas in other cells.

I will attach a screen shot when able.

View 2 Replies


ADVERTISEMENT

Calculate Future Value Of Monthly Recurring, Annually Increasing Payments

Sep 19, 2007

follows in paragraph 5 - but first, background!

I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate.

Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET($A$1,0,0,Term,1))-1),(1+Return_on_investment)^(Term-ROW(OFFSET($A$1,0,0,Term,1))+1))

(Example: $1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the $1000 is 10% per annum (Return_on_investment). The $1000 increases by 5% (Increase_in_payment) each year - i.e. 19 increases - answer: $89,632 (rounded))

This formula assumes that the payment is made at the beginning of the period.

Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis.

Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof - maybe even from the person who supplied it to me (I cannot see who did!) - and then I can start fiddling with it myself if answers do not come.

Two previous posts of mine that dealt with somewhat different issues on the same formula are:

Determine Present Value From Future Value

and

[url]

View 9 Replies View Related

Increasing/decreasing; Rate Of Decrease - Spreadsheet

Aug 24, 2006

I’m trying to make this:

Amount of money: 1000 (changeable)
Beginning # of units: 0,1 (changeable)
Delta: 200 (changeable)
Rate of decrease: 50% (changeable)

0,1 unit should be added for every 200$ increase (1000+200=1200; 1200+200=1400; 1400+500=1900; 1900+256=2156 etc)
0,1 unit should be taken away after decreasing by100$ (because the rate of decrease = 50% (changeable))

After increasing of 1000 + 200, quantity of units should also increase for 0,1, so it should be 0,2 (1200), 0,3(1400), 0,4(1600)….. Amount of money can increase not only by 200, but for any sum. If that sum is (for example) 500, we should increase by 0,2 etc.

But when this some decreasing we are using rate of decrease (for example 50%, (changeable):
0,4 units – 1600
0,3 units – 1500
0,2 units – 1400
0,1 unit – 1300

View 2 Replies View Related

Calculate The Monthly Repayments On A Loan Taken Out Over A Certain Amount Of Years

Feb 21, 2010

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?!

View 9 Replies View Related

Budget Spreadsheet With Calendar - Recurring Monthly Expenses

Feb 28, 2014

I have created an monthly budget spreadsheet on excel with a calendar. Formula to show the recurring expenses in the calendar? (Example: Rent/Mortgage on the 1st of every month).

This is what I have : Monthly Budget.xls‎

View 1 Replies View Related

Create Macro To Insert Monthly P&L Figures Into Spreadsheet

Sep 29, 2013

I am trying to create a macro to insert monthly P&L figures into a spreadsheet.

I am needing the macro to insert a column to the left of a column titled 'movement' each month and copy&paste my monthly figures in.

I have recorded the below macro but my inserted column is always moving into column E and pushing the prior month to the right, where I need it to always be situated to the immediate left of the movement column, e.g. if the macro is inserting September P&L data I need it to be situated between August data (currently in column E and movement data currently in row F and so on.

Column C D E FItem
July
August
Movement

Fees
26,500
28,000
2,000

Assets Ongoing
150,000
160,000
10,000

Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("P&L Current Month").Select
Range("D11:D127").Select

[Code] ...

View 1 Replies View Related

Autofill In Macro: Get A Function (sum) Of Several Cells To Be Automated In A Monthly Spreadsheet

Feb 10, 2009

I am trying to get a function (sum) of several cells to be automated in a monthly spreadsheet. I can get the function to work, but how do I get it to Autofil? The syntex seems to call for a range, which will be different every month. I can't figure out how to loop it, so I thought autofill would work.

View 3 Replies View Related

Creating Spreadsheet With Large Amount Of Data?

Nov 11, 2013

I am creating a spread sheet with a large amount of data.

In column a I have a list of managers, in B I have a list of employees and C, I have a list of activities that have been recorded throughout the day.

What I want to do is create a list (on a new sheet) so in column A that would bring back all the managers. Then in column B, bring back the employee that is tied to the manager from column A and finally in column C, bring back the activity that is tied to the employee.

I know how I can do this by doing list validation with INDIRECT, but with the large amount of data that I have this would take forever in a day.

Is there an easier and quicker way to do this?

View 1 Replies View Related

To Design A Spreadsheet To Allocate An Amount To Different Bands

Feb 9, 2009

I'm trying to design a spreadsheet to allocate an amount to different bands.

For example - I have the figure 1750.

Band 1 : 0-500 (1)
Band 2 : 501-1000 (0.8)
Band 3 : 1001-1500 (0.6)
Band 4 : 1501-2000 (0.4)

Each unit in each band has a value (in brackets). So as you can see, the value of a full Band 1 would be 500. But the next band is only worth 400 when full, and Band 3 is only worth 300. I want band 1 to fill up first, then band 2, then band 3, then finally band 4.

I would like to be able to allocate the figure 1750 to all the bands automatically - starting with band 1, then 2, then 3, then 4.

View 9 Replies View Related

Find Amount In Variable Call Locations That Change On Each Spreadsheet

Feb 15, 2012

I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?

I can provide a small copy of a workbook, if needed.

View 1 Replies View Related

Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet

Dec 27, 2011

I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.

If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.

View 4 Replies View Related

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 View Related

Pulling Data Into Two Columns Labeled “Monthly” & “Non-Monthly”

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

Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached

Jan 28, 2009

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.

View 9 Replies View Related

Reference Cell & Add Amount If Positive & Subtract Amount If Negative

Jun 5, 2008

Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:

I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.

View 4 Replies View Related

Macro For Base Amount And Net Amount Calculation

Nov 20, 2012

For the below macro.

ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030

A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.

For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.

View 1 Replies View Related

Increasing Rate Of Value?

Mar 27, 2014

I have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.

View 5 Replies View Related

Increasing A Cell Value By +1 Every Day

Oct 22, 2007

I'm just fiddling around with excel at the moment and have a created a cell with a value of 16. It represents the number of days an event has been running for.

I'm not sure how to make it increase by +1 every day without me having to open excel and change the value in the cell.

View 2 Replies View Related

Increasing Cells By 1/3 With Autofill

Jun 11, 2014

I'm trying to find a formula that increase a cell by 1/3 that will work with auto fill.

For Example if auto-filling a column:
3
3
3
4
4
4
5
5
5

Or if auto-filling a row:
3 3 3 4 4 4 5 5 5

View 11 Replies View Related

Increasing Numbers In Each New Cell

Nov 26, 2008

in a1 I have a number 1, a2 is a number 2. I want to keep on counting down to number 7691. Just do not know how to do this.

View 5 Replies View Related

Increasing Value Of Cell On Save

Sep 21, 2009

I have a worksheet that I need a piece of code for, Cell E1 is an amendment number that increases every time a new one is put out,
so they can be tracked.

Every time the sheet is saved the value in cell E1 needs to increase by 1.

View 2 Replies View Related

Subtract Amount From Cell Until It Reaches 0 Then Move And Subtract From Next Amount?

Apr 28, 2012

I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.

AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?

View 6 Replies View Related

Row Index Number Increasing Automatically

Mar 29, 2007

I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?

I am attaching a SAMPLE of data. The actual worksheet is much more larger.

Hlookup.xls

View 8 Replies View Related

How To Get 3D Sum Against Increasing Number Of Sheet Tabs

Mar 9, 2014

My wife and I run an apartment complex with 2 employees, a manager and a maintenance man.

We use excel to produce a paystub for these employees twice a month.

There are a number of variables including incentives for renting apartments, overtime, variable hours in a pay period etc.

We have a separate spreadsheet for each employee and a separate sheet tab for each pay period named for the pay date... e.g. the first paystub had one sheet named "Jan 15"... the next pay period the paystub had 2 sheets: "Jan 31" and "Jan 15". The third paystub had 3 sheets and so on.

The paystubs have a column with the numbers for the pay period and then a column with the year to date sum (for straight pay, over time, federal withholding, social security, medicare, state withholding, etc.)

Right now I edit the formulas for all these sums for each payperiod. e.g. =sum('Feb 15':'Jan 15'!B35) for the third pay period is editted to =sum('Feb 28':'Jan 15'!B35) for the fourth pay period.

I would like to be able to just copy the prior spreadsheet and not have to find and replace values in 11 spreadsheet cells for each pay period.

I have figured out how to get the name of the current spreadsheet to appear in a cell on that sheet using VBA... but not how to get that cell content to be part of the arguments for =sum(. on the formula bar...

View 2 Replies View Related

Increasing Value Of One Cell Based On Another Incremental Value

Feb 20, 2014

I should know this and I'm sure its something very simple that just wont come to me. I have two cells, one has "total billable hours" in a month, the second needs to calculate how many days based on that number. So it needs to increase by 1 for every multiplier of 24 in the first cell.

View 3 Replies View Related

Increasing Row Number In Regular Pattern

Jun 10, 2014

I want the first 60 rows of column C to be constant meaning C1, C2, C3, C4..C59 and after 60 rows it should start again with C1, C2, C3.....C59 rather than C60, C61, C62. In other words i+1 but after 60 rows i should be reset to 1 and then again increase by 1. how can i implement these changes

[Code] ......

View 2 Replies View Related

Macro To Print Rows That Keep Increasing?

Jul 31, 2009

I'm searching for a macro that will allow me to print rows that are constantly changing in number. Attached is a sample of my workbook. The workbook has worksheets by month. There are data from five sales people on each sheet so each sales person has his own section. I have a print button within each section so he can print only his section of the page. No problem creating that macro.

However, at least twice a week the sales people are adding rows or moving a row of data from one month to another month, so the print area is constantly changing.

View 4 Replies View Related

Increasing Value Of One Cell Based On Another Incremental Value

Nov 4, 2008

I have cell F15 which is blank by default, and cell D14 which pulls a value from another sheet (D14's value is =Info!X20). For D14's properties I have it set to show thirds (Custom Property "# ?/3"). I want to make D14 increase by 1/3 for every increment of 60 that F15 contains. For example, let's say D14 is 12. If F15 is 59, it won't change. If it's 60, D14 will be 12 1/3, and if its 180, it'll be 13. I think I'm close, but just can't quite get it.

View 8 Replies View Related

Expanding Increasing Phone Numbers?

May 20, 2012

I have thousands of phone numbers that hundreds of them are look like this:

123456-8 that means : 123456,123457,123458 or this
123456-57 that means : 123456,123457

There are not any standard of numbers after dash (-) in my data.

how can I expand them to new "rows"?

bob 123456-8 ->
bob 123456
bob 123457
bob 123458

View 9 Replies View Related

Row Index Number Increasing Automatically

Mar 29, 2007

I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?

I am attaching a SAMPLE of data. The actual worksheet is much more larger.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved