Get value from December given year

N

nevernine99

New Member
Joined
Dec 18, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello,
This is my first attempt at Xl2bb.

Could you please help me with a formula that can get value only from December month from year given ?
Need to calculate year-end balance which is in each year December month. But value can be YYYY format only in L3 column.
For example, in Q3 result would be 481647.92 (H14).

Thanks for your help in advance. Please let me know if there are any questions. Thanks again.
 

Attachments

  • Book14.png
    Book14.png
    84.6 KB · Views: 19

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Value based on year.xlsx
ABCDEFGHIJKLMNOPQR
1No.Payment DateInterest RatePayment DueExtra PaymentInterestPrincipalBalanceTotal InterestTotal PrincipalYearAverage % Interest RateExtra PaymentInterestPrincipalBalance
2
31January 1, 20202.00%10683.11100993.339789.78586210.2993.339789.7820202.58%100.0013890.12114352.08
42February 1, 20202.00%10683.11977.029706.09576504.11970.3519495.8720212.08%100.009296.53116799.43
53March 1, 20202.00%10683.11960.849722.27566781.92931.1929218.1420223.75%0.0011235.55117099.22
64April 1, 20202.00%10683.11944.649738.47557043.43875.8338956.61
75May 1, 20202.00%10683.11928.419854.7547188.74804.2448811.31
86June 1, 20203.00%10660.951367.979292.98537895.76172.2158104.29
97July 1, 20203.00%10660.951344.749316.21528579.57516.9567420.5
108August 1, 20203.00%10660.951321.459339.55192408838.476760
119September 1, 20203.00%10660.951298.19362.85509877.210136.586122.85
1210October 1, 20203.00%10660.951274.699386.26500490.911411.1995509.11
1311November 1, 20203.00%10660.951251.239409.72491081.212662.42104918.8
1412December 1, 20203.00%10660.951227.79433.25481647.913890.12114352.1
1513January 1, 20214.00%10875.151605.499269.66472378.315495.61123621.7
1614February 1, 20214.00%10875.151574.599300.56463077.717070.2132922.3
1715March 1, 20214.00%10875.151543.599331.56453746.118613.79142253.9
1816April 1, 20212.00%10474.49756.249718.25444027.919370.03151972.1
1917May 1, 20212.00%10474.49740.059734.44434293.520110.08161706.6
2018June 1, 20212.00%10474.49723.829750.67424542.820833.9171457.2
2119July 1, 20212.00%10474.49707.579766.92414775.921541.47181224.1
2220August 1, 20211.00%10294.51345.659948.8640482721887.12191173
2321September 1, 20211.00%10294.51337.369957.15394869.922224.48201130.2
2422October 1, 20211.00%10294.51329.069965.45384904.422553.54211095.6
2523November 1, 20211.00%10294.51100320.7510073.76374830.622874.29221169.4
2624December 1, 20211.00%10294.51312.369982.15364848.523186.65231151.5
2725January 1, 20222.00%10450.2608.089842.12355006.423794.73240993.6
2826February 1, 20222.00%10450.2591.689858.52345147.924386.41250852.2
2927March 1, 20222.00%10450.2575.259874.95335272.924961.66260727.1
3028April 1, 20222.00%10450.2558.799891.41325381.525520.45270618.5
3129May 1, 20222.00%10450.2542.39907.9315473.626062.75280526.4
3230June 1, 20225.00%10869.111314.479554.6430591927377.22290081.1
3331July 1, 20225.00%10869.111274.669594.45296324.528651.88299675.5
Sheet1
Cell Formulas
RangeFormula
M3:M5M3=AVERAGEIFS($C$3:$C$482,$B$3:$B$482,">="&DATE(L3,1,1),$B$3:$B$482,"<="&DATE(L3,12,31))
N3:P5N3=SUMIFS(E$3:E$512,$B$3:$B$512,">="&DATE($L3,1,1),$B$3:$B$512,"<="&DATE($L3,12,31))
Cells with Data Validation
CellAllowCriteria
Q3Any value
 
Upvote 0
Welcome to the Board!

Based on your example, put this formula in cell Q3:
Excel Formula: 
=VLOOKUP(DATEVALUE("December 1, " & $L3),$B$3:$J$512,7,0)
 
Upvote 0
Solution
You are welcome!
Glad I was able to help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Vesting Schedule - Formula/Formatting
Replies
2
Views
229
ExcelUser18
E
M
  • Question Question
Pro rata formula
Replies
4
Views
307
6StringJazzer
6StringJazzer
H
  • Question Question
Creation of a dashboard by using calculated predicted values and actual tip values.
Replies
0
Views
76
Harold14
H
B
  • Solved
Show Monthly up to a point
Replies
2
Views
108
FormR
FormR
A
  • Question Question
Date setting according to year and month selection
Replies
1
Views
316
LazyBug
LazyBug

Forum statistics

Threads
1,224,737
Messages
6,180,653
Members
452,992
Latest member
TokugawaIesuma

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top