Formula that multiplies only every year

**tryingtoexcelatexcel** · 06-04-2015, 07:30 AM

Hi Everyone,

So this is my first post here and I was hoping someone could help. I will try to explain the best I can.

Table 1

Base 1 2 3 4 5 6 7 8 Total
Jan-15 1.00 1.00 - - - - - - 2.00
Feb-15 1.00 1.00 - - - - - - 2.00
Mar-15 1.00 1.00 - - - - - - 2.00
Apr-15 1.00 1.00 1.00 - - - - - 3.00
May-15 1.00 1.00 1.00 - - - - - 3.00
Jun-15 1.00 1.00 1.00 - - - - - 3.00
Jul-15 1.00 1.00 1.00 - - - - - 3.00
Aug-15 1.00 1.00 1.00 - - - - - 3.00
Sep-15 1.00 1.00 1.00 - - - - - 3.00
Oct-15 1.00 1.00 1.00 - - - - - 3.00
Nov-15 1.00 1.00 1.00 - - - - - 3.00
Dec-15 1.00 1.00 1.00 - - - - - 3.00
Jan-16 1.00 1.00 1.00 - - - - - 3.00
Feb-16 1.00 1.00 1.00 - - - - - 3.00
Mar-16 1.00 1.00 1.00 - - - - - 3.00
Apr-16 1.00 1.00 1.00 - - - - - 3.00
May-16 1.00 1.00 1.00 - - - - - 3.00
Jun-16 1.00 1.00 1.00 - - - - - 3.00
Jul-16 1.00 1.00 1.00 - - - - - 3.00
Aug-16 1.00 1.00 1.00 - - - - - 3.00
Sep-16 1.00 1.00 1.00 - - - - - 3.00
Oct-16 1.00 1.00 1.00 - - - - - 3.00
Nov-16 1.00 1.00 1.00 - - - - - 3.00
Dec-16 1.00 1.00 1.00 - - - - - 3.00
Jan-17 1.00 1.00 1.00 1.00 - - - - 4.00
Feb-17 1.00 1.00 1.00 1.00 - - - - 4.00
Mar-17 1.00 1.00 1.00 1.00 - - - - 4.00
Apr-17 1.00 1.00 1.00 1.00 - - - - 4.00
May-17 1.00 1.00 1.00 1.00 - - - - 4.00
Jun-17 1.00 1.00 1.00 1.00 1.00 - - - 5.00
Jul-17 1.00 1.00 1.00 1.00 1.00 - - - 5.00
Aug-17 1.00 1.00 1.00 1.00 1.00 - - - 5.00
Sep-17 1.00 1.00 1.00 1.00 1.00 - - - 5.00
Oct-17 1.00 1.00 1.00 1.00 1.00 - - - 5.00
Nov-17 1.00 1.00 1.00 1.00 1.00 - - - 5.00
Dec-17 1.00 1.00 1.00 1.00 1.00 - - - 5.00
Jan-18 1.00 1.00 1.00 1.00 1.00 1.00 - - 6.00
Feb-18 1.00 1.00 1.00 1.00 1.00 1.00 - - 6.00
Mar-18 1.00 1.00 1.00 1.00 1.00 1.00 - - 6.00
Apr-18 1.00 1.00 1.00 1.00 1.00 1.00 - - 6.00
May-18 1.00 1.00 1.00 1.00 1.00 1.00 - - 6.00
Jun-18 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Jul-18 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Aug-18 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Sep-18 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Oct-18 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Nov-18 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Dec-18 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Jan-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 - 7.00
Feb-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Mar-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Apr-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
May-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jun-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jul-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Aug-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Sep-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Oct-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Nov-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Dec-19 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jan-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Feb-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Mar-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Apr-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
May-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jun-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jul-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Aug-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Sep-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Oct-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Nov-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Dec-20 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jan-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Feb-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Mar-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Apr-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
May-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jun-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Jul-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Aug-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Sep-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Oct-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Nov-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00
Dec-21 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8.00

So this above table is my base case that I would like to use so I can just change the 1's to 0's and have the rest of my tables change as well

Table 2

Escalation 3% 1 2 3 4 5 6 7 8 Total
Jan-15 1.00 1.00 - - - - - - 2.00
Feb-15 1.00 1.00 - - - - - - 2.00
Mar-15 1.00 1.00 - - - - - - 2.00
Apr-15 1.00 1.00 1.00 - - - - - 3.00
May-15 1.00 1.00 1.00 - - - - - 3.00
Jun-15 1.00 1.00 1.00 - - - - - 3.00
Jul-15 1.00 1.00 1.00 - - - - - 3.00
Aug-15 1.00 1.00 1.00 - - - - - 3.00
Sep-15 1.00 1.00 1.00 - - - - - 3.00
Oct-15 1.00 1.00 1.00 - - - - - 3.00
Nov-15 1.03 1.00 1.00 - - - - - 3.03
Dec-15 1.03 1.00 1.00 - - - - - 3.00
Jan-16 1.03 1.00 1.00 - - - - - 3.00
Feb-16 1.03 1.03 1.00 - - - - - 3.06
Mar-16 1.03 1.03 1.00 - - - - - 3.06
Apr-16 1.03 1.03 1.03 - - - - - 3.09
May-16 1.03 1.03 1.03 - - - - - 3.09
Jun-16 1.03 1.03 1.03 - - - - - 3.09
Jul-16 1.03 1.03 1.03 - - - - - 3.09
Aug-16 1.03 1.03 1.03 - - - - - 3.09
Sep-16 1.03 1.03 1.03 - - - - - 3.09
Oct-16 1.03 1.03 1.03 - - - - - 3.09
Nov-16 1.03 1.03 1.03 - - - - - 3.09
Dec-16 1.03 1.03 1.03 - - - - - 3.09
Jan-17 1.06 1.03 1.03 1.00 - - - - 4.12
Feb-17 1.06 1.06 1.03 1.00 - - - - 4.15
Mar-17 1.06 1.06 1.03 1.00 - - - - 4.15
Apr-17 1.06 1.06 1.06 1.00 - - - - 4.18
May-17 1.06 1.06 1.06 1.00 - - - - 4.18
Jun-17 1.06 1.06 1.06 1.00 1.00 - - - 5.18
Jul-17 1.06 1.06 1.06 1.00 1.00 - - - 5.18
Aug-17 1.06 1.06 1.06 1.00 1.00 - - - 5.18
Sep-17 1.06 1.06 1.06 1.00 1.00 - - - 5.18
Oct-17 1.06 1.06 1.06 1.00 1.00 - - - 5.18
Nov-17 1.06 1.06 1.06 1.00 1.00 - - - 5.18
Dec-17 1.06 1.06 1.06 1.00 1.00 - - - 5.18
Jan-18 1.09 1.06 1.06 1.03 1.00 1.00 - - 6.24
Feb-18 1.09 1.09 1.06 1.03 1.00 1.00 - - 6.28
Mar-18 1.09 1.09 1.06 1.03 1.00 1.00 - - 6.28
Apr-18 1.09 1.09 1.09 1.03 1.00 1.00 - - 6.31
May-18 1.09 1.09 1.09 1.03 1.00 1.00 - - 6.31
Jun-18 1.09 1.09 1.09 1.03 1.03 1.00 1.00 - 7.34
Jul-18 1.09 1.09 1.09 1.03 1.03 1.00 1.00 - 7.34
Aug-18 1.09 1.09 1.09 1.03 1.03 1.00 1.00 - 7.34
Sep-18 1.09 1.09 1.09 1.03 1.03 1.00 1.00 - 7.34
Oct-18 1.09 1.09 1.09 1.03 1.03 1.00 1.00 - 7.34
Nov-18 1.09 1.09 1.09 1.03 1.03 1.00 1.00 - 7.34
Dec-18 1.09 1.09 1.09 1.03 1.03 1.00 1.00 - 7.34
Jan-19 1.13 1.09 1.09 1.06 1.03 1.03 1.00 - 7.43
Feb-19 1.13 1.13 1.09 1.06 1.03 1.03 1.00 1.00 8.46
Mar-19 1.13 1.13 1.09 1.06 1.03 1.03 1.00 1.00 8.46
Apr-19 1.13 1.13 1.13 1.06 1.03 1.03 1.00 1.00 8.50
May-19 1.13 1.13 1.13 1.06 1.03 1.03 1.00 1.00 8.50
Jun-19 1.13 1.13 1.13 1.06 1.06 1.03 1.03 1.00 8.56
Jul-19 1.13 1.13 1.13 1.06 1.06 1.03 1.03 1.00 8.56
Aug-19 1.13 1.13 1.13 1.06 1.06 1.03 1.03 1.00 8.56
Sep-19 1.13 1.13 1.13 1.06 1.06 1.03 1.03 1.00 8.56
Oct-19 1.13 1.13 1.13 1.06 1.06 1.03 1.03 1.00 8.56
Nov-19 1.13 1.13 1.13 1.06 1.06 1.03 1.03 1.00 8.56
Dec-19 1.13 1.13 1.13 1.06 1.06 1.03 1.03 1.00 8.56
Jan-20 1.16 1.13 1.13 1.09 1.06 1.06 1.03 1.00 8.65
Feb-20 1.16 1.16 1.13 1.09 1.06 1.06 1.03 1.03 8.72
Mar-20 1.16 1.16 1.13 1.09 1.06 1.06 1.03 1.03 8.72
Apr-20 1.16 1.16 1.16 1.09 1.06 1.06 1.03 1.03 8.75
May-20 1.16 1.16 1.16 1.09 1.06 1.06 1.03 1.03 8.75
Jun-20 1.16 1.16 1.16 1.09 1.09 1.06 1.06 1.03 8.82
Jul-20 1.16 1.16 1.16 1.09 1.09 1.06 1.06 1.03 8.82
Aug-20 1.16 1.16 1.16 1.09 1.09 1.06 1.06 1.03 8.82
Sep-20 1.16 1.16 1.16 1.09 1.09 1.06 1.06 1.03 8.82
Oct-20 1.16 1.16 1.16 1.09 1.09 1.06 1.06 1.03 8.82
Nov-20 1.16 1.16 1.16 1.09 1.09 1.06 1.06 1.03 8.82
Dec-20 1.16 1.16 1.16 1.09 1.09 1.06 1.06 1.03 8.82
Jan-21 1.19 1.16 1.16 1.13 1.09 1.09 1.06 1.03 8.91
Feb-21 1.19 1.19 1.16 1.13 1.09 1.09 1.06 1.06 8.98
Mar-21 1.19 1.19 1.16 1.13 1.09 1.09 1.06 1.06 8.98
Apr-21 1.19 1.19 1.19 1.13 1.09 1.09 1.06 1.06 9.01
May-21 1.19 1.19 1.19 1.13 1.09 1.09 1.06 1.06 9.01
Jun-21 1.19 1.19 1.19 1.13 1.13 1.09 1.09 1.06 9.08
Jul-21 1.19 1.19 1.19 1.13 1.13 1.09 1.09 1.06 9.08
Aug-21 1.19 1.19 1.19 1.13 1.13 1.09 1.09 1.06 9.08
Sep-21 1.19 1.19 1.19 1.13 1.13 1.09 1.09 1.06 9.08
Oct-21 1.19 1.19 1.19 1.13 1.13 1.09 1.09 1.06 9.08
Nov-21 1.19 1.19 1.19 1.13 1.13 1.09 1.09 1.06 9.08
Dec-21 1.19 1.19 1.19 1.13 1.13 1.09 1.09 1.06 9.08

This is what I am trying to achieve with a formula, so linking to table 1, products 1-8 will be coming in at different times of the year, some will arrive in January 15, some in Feb 19, but each year the product will increase by 3%, I'm trying to figure out a formula that allows table 2's products to know that 1 year has passed and it is time to increase by 3%.

The reason I am linking to table 1 is because I have more than 2 tables but the concept is the same. I would like to be able to change table 1's, 1 and 0's so they will affect table 2,3,4... and then in tables 2,3,4 have a formula know that a certain product has now reached 1 year and needs to escalate by 3% independently of other products since each one will be arriving at different time periods and their yearly mature month/year will be different.

I really hope someone can help or else it will be back to manually changing each table when there is a change to the product arrival date :]

Thank you very much

Oh no.. I just realized the table looks really funny after I submit. I'm so sorry I'm not sure how to straighten it out.

**oeldere** · 06-04-2015, 07:38 AM

You getadd a small excel file, on the forum, without confidential information.

Add manualy the expected result in your file.

**Special-K** · 06-04-2015, 08:05 AM

I think oedere means post an Excel file here, remove any sensitive information.

**tryingtoexcelatexcel** · 06-04-2015, 11:54 AM

Book1.xlsx Book1.xlsx

Hi Oeldere and Special K, thank you so much for replying, here is an example of what I am trying to achieve.

I hope I attached it correctly.

**oeldere** · 06-04-2015, 12:53 PM

with vlookup.

Altough I get a differant answer as you expect.

See the attached file.

**tryingtoexcelatexcel** · 06-04-2015, 01:02 PM

Ah I see thanks so much Oeldere, but looks like I need to create a vlookup for every product and for every schedule as well. So if the date of the incoming product were to change then I would need to go in and manually change all the vlookup dates.

I was wondering if there was a more dynamic way just to control everything from the base case schedule while still allowing each schedule to increase or decrease year over year.

**oeldere** · 06-04-2015, 01:14 PM

With index / match

See the green cells in the attached file.

**tryingtoexcelatexcel** · 06-04-2015, 01:57 PM

Oh wow, that is a great solution, I guess the base case won't be used then and instead utilize dates on top for index/match

Thank you very very very much Oeldere, your solution is very helpful and I really appreciate it. Thanks again.

**oeldere** · 06-04-2015, 02:01 PM

Thanks for the reply. Glad I could help.

Will you mark the question solved if it is solved?

You can add rep(utationpoints) to the one who helped you by clicking on the star on the left side.

**tryingtoexcelatexcel** · 06-04-2015, 02:12 PM

Ok, done :] thanks again!

**tryingtoexcelatexcel** · 06-07-2015, 10:24 PM

Hi Oeldere,

I was wondering, would it be possible to add "offset" to index match so it would move every 12 months?

**oeldere** · 06-08-2015, 02:26 AM

I don't understand the question.

What is the need or advantage of that?

**tryingtoexcelatexcel** · 06-08-2015, 05:33 AM

I'm not too sure, I previously learned how to add offset to my index match function which helped return what I needed dynamically, it helped update my match reference so in this case it could help update according to year maybe? I'm actually not too familiar with the offset function myself, I was just wondering if there was some way to incorporate it into the index match function.

**oeldere** · 06-08-2015, 05:35 AM

I see no advantage to use offset in your question.

Maybe another forummember does, an will show me the light.

Formula that multiplies only every year

LinkBack

Thread Tools

Rate This Thread

Display

Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Re: Formula that multiplies only every year

Thread Information

Users Browsing this Thread

Similar Threads

Writing a formula that references a column (length), and then multiplies it by a set price

Year On Year Chart series created using named range with formula does not work.

A formula that adds and multiplies a range of data if certain criteria it met

IF formula that also multiplies

Formula that multiplies X for every Y unit

Bookmarks

Bookmarks

Posting Permissions