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: 7

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.
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

A
  • Question
Date setting according to year and month selection
Replies
1
Views
140
LazyBug
LazyBug
B
  • Question
Extract value based on actual month & year
Replies
4
Views
139
bigellow
B
G
  • Question
Pivot table (Diff from Previous) Year on Year, if current month is blank, then return blank?
Replies
0
Views
136
gazmoz17
G
itsgrady
  • Solved
Return weekday only for a given month
Replies
3
Views
287
Fluff
Fluff
B
  • Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
185
B-radK
B

Forum statistics

Threads
1,215,028
Messages
6,122,753
Members
449,094
Latest member
dsharae57

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