Calculate value based on date = date in another cell

AMP128

New Member
Joined
Feb 21, 2019
Messages
2
Hi all,
How do I calculate vested shares, in a particular month, based on the vesting date?

For example, I am trying to solve for Cells I2 and N2, the correct answer will be 25,000, however I don't know how to set up the formula to calculate based on the date in row 1.

D2:G2 are the vesting dates.
ABCDEFGHIJKLMNO
1

NameAmountVesting Terms1st2nd3rd4thJan-17Feb-17Mar-17Apr-17May-17Jun-17
2Employee100,00025%Jan-17Jun-17Jun-18Jun-19
3
4

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Hi AMP128,

I made some assumptions since it was not completely clear to me. I am assuming that in Jan-2017 Employee is vested at 25%, in Jun-2017 vested at 50%, Jun-2018 vested at 75%, and Jun-2019 vested at 100%. If that is the case, one possible solution is to use the Match function.

The equation to put in cell I2 would be: =MATCH(J1,$E$2:$H$2,1)*$D$2*$C$2

You can copy that equation to the cells to the right of it.

Hope that helps,

Doug
 

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Sorry my ranges of off by one because I copied your data from above to Excel and had an extra column so the updated formula is: =MATCH(I1,$D$2:$G$2,1)*$C$2*$B$2

Doug
 

Watch MrExcel Video

Forum statistics

Threads
1,109,161
Messages
5,527,156
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top