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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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

M
  • Question Question
Pro rata formula
Replies
4
Views
252
6StringJazzer
6StringJazzer
H
  • Question Question
Creation of a dashboard by using calculated predicted values and actual tip values.
Replies
0
Views
66
Harold14
H
B
  • Solved
Show Monthly up to a point
Replies
2
Views
102
FormR
FormR
A
  • Question Question
Date setting according to year and month selection
Replies
1
Views
312
LazyBug
LazyBug
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
662
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,711
Messages
6,174,025
Members
452,542
Latest member
Bricklin

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