# Is there a formula that changes a multiplier based on the year?

#### markcue

##### New Member
I'm trying to figure out if I can have a single cell change the multiplier based on the year.
Example:
 A B C D E 1 YEAR START STOP HOURS MEAL (DOM) MEAL (INT'L) 2 2014 6 AM 4 PM 10 22.00 26.50 3 2016 6 AM 4 PM 10 23.50 27.00

<tbody>
</tbody>

I'm multiplying C2 x the value of D2 and E2

D2 has a value of 2.20 (based on the year 2014). It goes up .05 every year till 2017

E2 has a value of 2.60 (based on the year of 2014). It goes up .05 ever year till 2017

If the year is 2014, the value of C2 (hours) multiplies the value of D2 (2.20 based on the year). If the year is 2016, the value of C2 (hours) multiplies the value of D2 (2.30 based on the year).

How can I make D2 or E2 reflect the multiplier based on what year it is?

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I show the column references different than you:

Code:
``````  A	  B		  C		  D	  E	 	  F
YEAR	START		STOP		HOURS	MEAL (DOM)	MEAL (INT'L)
2014	6:00 AM		4:00 PM		10	22.00		26.00
2016	6:00 AM		4:00 PM		10	23.00		27.00``````

ANd based on your descriptions my results are different....

E2: =(2.2+((\$A2-2014)*0.05))*\$D2
F2: =(2.6+((\$A2-2014)*0.05))*\$D2

Since Year is not in an actual column, I'm assuming that you want this change with the actual date. So that when today's date becomes the year 2015 on January 1, the formulas should change. I am also assuming that after the year 2017, the the value of the multiplier remains steady at .15.

To be clear, I don't quite understand how you got the values of 26.50 or 23.50 in cells E2 and D3, so I might have misunderstood something. Please clarify if that's the case. Given the current value of C2, my formula will return 22 and 26 in 2014, 22.5 and 26.5 in 2015, 23 and 27 in 2016, and 23.5 and 27.5 from 2017 and on.

Excel 2010
ABCDE
1ABCDE
2STARTSTOPHOURSMEAL (DOM)MEAL (INT'L)
36:00 AM4:00 PM102226
Sheet4
Cell Formulas
RangeFormula
D3=C3*(2.2+MIN((YEAR(TODAY())-2014)*0.05,0.15))
E3=C3*(2.6+MIN((YEAR(TODAY())-2014)*0.05,0.15))

Replies
16
Views
1K
Replies
2
Views
510
Replies
20
Views
2K
Replies
10
Views
1K
Replies
2
Views
2K

1,219,878
Messages
6,150,716
Members
450,982
Latest member
L2LExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back