Need a formula to get start balance and end balance by year

NeedExcelHelp2021

New Member
Joined
Apr 17, 2021
Messages
35
Office Version
  1. 365
Platform
  1. Windows
I have a lot of cells so a formula that can do this would be a great help, and can you make it work on filtered cells Thank You.


Book1
ABCDEFGH
12009$ 10,000YearStart BalanceEnd Balance
22009$ 15,0002009$ 10,000$ 30,000
32009$ 20,0002010$ 35,000$ 50,000
42009$ 40,0002011$ 70,000$ 105,000
52009$ 30,000
62010$ 35,000
72010$ 40,000
82010$ 45,000
92010$ 50,000
102010$ 60,000
112010$ 55,000
122010$ 50,000
132011$ 70,000
142011$ 75,000
152011$ 80,000
162011$ 85,000
172011$ 160,000
182011$ 150,000
192011$ 100,000
202011$ 105,000
21
22
23
Sheet1
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
This will work in Power Query so long as the the ending balance is the max balance and the starting balance is the min balance

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"min", each List.Min([Column2]), type nullable number}, {"max", each List.Max([Column2]), type nullable number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Column1", "year"}, {"min", "start"}, {"max", "end"}})
in
    #"Renamed Columns"
 
Upvote 0
In E2:

Excel Formula:
=INDEX($B$1:$B$20,MIN(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))

In F2:
Excel Formula:
=INDEX($B$1:$B$20,MAX(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))
 
Upvote 0
In E2:

Excel Formula:
=INDEX($B$1:$B$20,MIN(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))

In F2:
Excel Formula:
=INDEX($B$1:$B$20,MAX(IF($A$1:$A$20=D2,SEQUENCE(20),"-")))
[/QUOTE]
Thank You it's works, but not on filtered cells ... still Thank You Much Appreciated.
 
Upvote 0
Did you test/try my suggestion? This might help you to understand it better.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Upvote 0
Did you test/try my suggestion? This might help you to understand it better.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
Thank You So Much

I watched the videos....i always wander if i can combine my spreadsheets data, this will be great help to me THANK YOU.
 
Upvote 0
a formula that can do this would be a great help, and can you make it work on filtered cells
See if this does what you want. Since you said a filtered list I assumed that the data actually has a header row.

List unfiltered:

21 10 03.xlsm
ABCDEF
1YearBalanceYearStart BalanceEnd Balance
220091000020091000030000
320091500020103500050000
4200920000201170000105000
5200940000
6200930000
7201035000
8201040000
9201045000
10201050000
11201060000
12201055000
13201050000
14201170000
15201175000
16201180000
17201185000
182011160000
192011150000
202011100000
212011105000
22
Start End Filtered
Cell Formulas
RangeFormula
E2:E4E2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4F2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)


List filtered:

21 10 03.xlsm
ABCDEF
1YearBalanceYearStart BalanceEnd Balance
220091000020091000040000
320091500020104000055000
42009200002011150000150000
5200940000
8201040000
9201045000
12201055000
192011150000
22
Start End Filtered
Cell Formulas
RangeFormula
E2:E4E2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4F2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)
 
Upvote 0
Solution
See if this does what you want. Since you said a filtered list I assumed that the data actually has a header row.

List unfiltered:

21 10 03.xlsm
ABCDEF
1YearBalanceYearStart BalanceEnd Balance
220091000020091000030000
320091500020103500050000
4200920000201170000105000
5200940000
6200930000
7201035000
8201040000
9201045000
10201050000
11201060000
12201055000
13201050000
14201170000
15201175000
16201180000
17201185000
182011160000
192011150000
202011100000
212011105000
22
Start End Filtered
Cell Formulas
RangeFormula
E2:E4E2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4F2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)


List filtered:

21 10 03.xlsm
ABCDEF
1YearBalanceYearStart BalanceEnd Balance
220091000020091000040000
320091500020104000055000
42009200002011150000150000
5200940000
8201040000
9201045000
12201055000
192011150000
22
Start End Filtered
Cell Formulas
RangeFormula
E2:E4E2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,1)
F2:F4F2=XLOOKUP(1,(A$2:A$21=D2)*SUBTOTAL(2,OFFSET(A$1,SEQUENCE(ROWS(A$2:A$21)),,1)),B$2:B$21,,,-1)
OMG!!! Works Brilliantly.

THANK YOU SO MUCH HAVE A GREAT DAY.
 
Upvote 0
You're welcome. Glad it worked for you. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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