Find the value based on date

P

pyclen

Board Regular
Joined
Jan 17, 2022
Messages
85
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi All,

I have the below table from the Bureau of Labor Statistics (Bureau of Labor Statistics Data) showing the pricing index for a given month.

1663934688733.png


To calculate the increase from one month to another (same or different year) I am using this table with the change being calculated using
Excel Formula: 
=ROUND(((D8-D7)/D7)*100,1)
DateValue
Initial Index ValueMay-20179.9 (this is D7 in above formula)
Current Index ValueMar-22281.396 (this is D8 in above formula)
% Change56.4

As of now I am changing the months manually as well as finding the corresponding value for the month for this calculation.
How can I get it to work

1. more automatically, i.e., I select the corresponding months and XL will populate the corresponding values in the last column
2. be dynamic, as every month new data gets published (I have the above table linked to the web source, so once a month I refresh the data for this table)

Any help is highly appreciated
 
Sort by date Sort by votes
pyclen said:
I have everything formatted as you said, same result
maybe attach the XL file you have so I can compare what is wrong w/my file or where I fail to format correctly

B3:m3 all is formatted as MMM
Click to expand...
In that case post your current XL2BB with value error - Let me check where the problem could be.
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
new cost 23Sep2022.xlsm
ABCDEFGHIJKLMNOPQRS
1
2
3YearJanFebMarAprMayJunJulAugSepOctNovDecDateValue
42012169.4169.4169.4169169.7169.5172.7173.7173.4173.9173.7173.8Initial Index Value44896#VALUE!
52013179.9181.0181.5181.6182.5183.4185.9185.7181.7182.1182.1184.0Current Index Value43800280.279
62014192.8193.2192.8192.3193.2195.4197.3197.7197.6199.2199.2201.6
72015207.8207.9208.0207.7208.5216216.1216.1216.2216.2216.1217.2% Change#VALUE!
82016227.8228.6228.6228.9229232232.5233.5233.7234.4234.4234.7
92017243.7243.7243.7243.7244.7247.1247.4247.5245.5250.3250.2250.5
102018257.8257.8257.8257.5257.2257.9260.3259.1259.4260.5262.3263.2
112019267.9267.9268.6269268.5268.2268.3268.4268.4268.5268.4268.1
122020273.3274.3274.6274.6274.5274.5274.8274.6272.8272.8272.8272.9
132021277.0277.4278.1277.6277.6277.6276.178276.244276.365276.538276.583276.506(P)
142022280.279(P)281.506(P)282.095(P)
15
16
Sheet2
Cell Formulas
RangeFormula
R4R4=INDEX($A$3:$M$14,MATCH(YEAR(Q4),$A$3:$A$14,0),MATCH(MONTH(Q4),MONTH($A$3:$M$3),0))
R7R7=ROUND(((R5-R4)/R4)*100,1)
Named Ranges
NameRefers ToCells
ExternalData_1=Sheet2!$A$3:$M$14R4
 
Upvote 0
What I notice in Cell B3:M3 you haven't put dates

Put dates in those cells as Jan 1, Feb 1 and so on...

Hopefully it shall work then
 
Upvote 0
same result, please share your copy so I see what is wrong as honestly I am formatting things as date, custom etc and nothing works
the upper half is original and the lower is the new.

I can live w/clicking to select things if this doesn;t work


new cost 23Sep2022.xlsm
ABCDEFGHIJKLMNOPQRS
1
2
3YearJanFebMarAprMayJunJulAugSepOctNovDecDateValue
42012169.4169.4169.4169169.7169.5172.7173.7173.4173.9173.7173.8Initial Index Value44896#VALUE!
52013179.9181.0181.5181.6182.5183.4185.9185.7181.7182.1182.1184.0Current Index Value43800280.279
62014192.8193.2192.8192.3193.2195.4197.3197.7197.6199.2199.2201.6
72015207.8207.9208.0207.7208.5216216.1216.1216.2216.2216.1217.2% Change#VALUE!
82016227.8228.6228.6228.9229232232.5233.5233.7234.4234.4234.7
92017243.7243.7243.7243.7244.7247.1247.4247.5245.5250.3250.2250.5
102018257.8257.8257.8257.5257.2257.9260.3259.1259.4260.5262.3263.2
112019267.9267.9268.6269268.5268.2268.3268.4268.4268.5268.4268.1
122020273.3274.3274.6274.6274.5274.5274.8274.6272.8272.8272.8272.9
132021277.0277.4278.1277.6277.6277.6276.178276.244276.365276.538276.583276.506(P)
142022280.279(P)281.506(P)282.095(P)
15
16
17
18YearJanFebMarAprMayJunJulAugSepOctNovDecDateValue
192012169.4169.4169.4169169.7169.5172.7173.7173.4173.9173.7173.8Initial Index Value44896#VALUE!
202013179.9181.0181.5181.6182.5183.4185.9185.7181.7182.1182.1184.0Current Index Value43800280.279
212014192.8193.2192.8192.3193.2195.4197.3197.7197.6199.2199.2201.6
222015207.8207.9208.0207.7208.5216216.1216.1216.2216.2216.1217.2% Change#VALUE!
232016227.8228.6228.6228.9229232232.5233.5233.7234.4234.4234.7
242017243.7243.7243.7243.7244.7247.1247.4247.5245.5250.3250.2250.5
252018257.8257.8257.8257.5257.2257.9260.3259.1259.4260.5262.3263.2
262019267.9267.9268.6269268.5268.2268.3268.4268.4268.5268.4268.1
272020273.3274.3274.6274.6274.5274.5274.8274.6272.8272.8272.8272.9
282021277.0277.4278.1277.6277.6277.6276.178276.244276.365276.538276.583276.506(P)
292022280.279(P)281.506(P)282.095(P)00000000 
30
Sheet2
Cell Formulas
RangeFormula
R4R4=INDEX($A$3:$M$14,MATCH(YEAR(Q4),$A$3:$A$14,0),MATCH(MONTH(Q4),MONTH($A$18:$M$18),0))
R7,R22R7=ROUND(((R5-R4)/R4)*100,1)
B19:M29B19=B4
R19R19=INDEX($A$18:$M$29,MATCH(YEAR(Q19),$A$18:$A$29,0),MATCH(MONTH(Q19),MONTH($A$18:$M$18),0))
Named Ranges
NameRefers ToCells
ExternalData_1=Sheet2!$A$3:$M$14R4
 
Upvote 0
I have checked the errors and rectified them. Now no need to put dates in B3:M3. Putting Month Names shall do.

Please check and revert.

All Records.xlsb
ABCDEFGHIJKLMNOPQR
1
2
3YearJanFebMarAprMayJunJulAugSepOctNovDecDateValue
42012169.4169.4169.4169169.7169.5172.7173.7173.4173.9173.7173.8Initial Index Value448960
52013179.9181181.5181.6182.5183.4185.9185.7181.7182.1182.1184Current Index Value43800268.1
62014192.8193.2192.8192.3193.2195.4197.3197.7197.6199.2199.2201.6
72015207.8207.9208207.7208.5216216.1216.1216.2216.2216.1217.2% Change#DIV/0!
82016227.8228.6228.6228.9229232232.5233.5233.7234.4234.4234.7
92017243.7243.7243.7243.7244.7247.1247.4247.5245.5250.3250.2250.5
102018257.8257.8257.8257.5257.2257.9260.3259.1259.4260.5262.3263.2
112019267.9267.9268.6269268.5268.2268.3268.4268.4268.5268.4268.1
122020273.3274.3274.6274.6274.5274.5274.8274.6272.8272.8272.8272.9
132021277277.4278.1277.6277.6277.6276.178276.244276.365276.538276.583276.506(P)
142022280.279(P)281.506(P)282.095(P)
15
16
17
18YearJanFebMarAprMayJunJulAugSepOctNovDecDateValue
192012169.4169.4169.4169169.7169.5172.7173.7173.4173.9173.7173.8Initial Index Value2/1/21277.4
202013179.9181181.5181.6182.5183.4185.9185.7181.7182.1182.1184Current Index Value2/1/19267.9
212014192.8193.2192.8192.3193.2195.4197.3197.7197.6199.2199.2201.6
222015207.8207.9208207.7208.5216216.1216.1216.2216.2216.1217.2% Change-3.4
232016227.8228.6228.6228.9229232232.5233.5233.7234.4234.4234.7
242017243.7243.7243.7243.7244.7247.1247.4247.5245.5250.3250.2250.5
252018257.8257.8257.8257.5257.2257.9260.3259.1259.4260.5262.3263.2
262019267.9267.9268.6269268.5268.2268.3268.4268.4268.5268.4268.1
272020273.3274.3274.6274.6274.5274.5274.8274.6272.8272.8272.8272.9
282021277277.4278.1277.6277.6277.6276.178276.244276.365276.538276.583276.506(P)
292022280.279(P)281.506(P)282.095(P)000000000
Sheet1
Cell Formulas
RangeFormula
R4:R5R4=INDEX($A$3:$M$14,MATCH(YEAR(Q4),$A$3:$A$14,0),MATCH(TEXT(Q4,"MMM"),$A$18:$M$18,0))
R7,R22R7=ROUND(((R5-R4)/R4)*100,1)
B19:M29B19=B4
R19:R20R19=INDEX($A$18:$M$29,MATCH(YEAR(Q19),$A$18:$A$29,0),MATCH(TEXT(Q19,"MMM"),$A$18:$M$18,0))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula to 2-way look up a unique identifier and a date, then take the highest number from another column
Replies
3
Views
190
MrsFraser07
M
M
  • Solved
Get values from specific date ranges
Replies
5
Views
413
kevin9999
K
J
  • Solved
Sum FTEs based on start/end date
Replies
3
Views
284
etaf
etaf
B
  • Question
Extract value based on actual month & year
Replies
4
Views
250
bigellow
B
B
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
330
Burrgogi
B

Forum statistics

Threads
1,216,747
Messages
6,132,483
Members
449,729
Latest member
davelevnt

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