shortning formula

shamsu203

New Member
Joined
Jun 12, 2014
Messages
33
Hi
This formula is upto to year 25 . when I extend this to 30 the formula will be become large. Anyway of shorting this depending on the cells in b2 and e 2 the yearly or monthly data is populated in the respective cells.
=IF(OR(AND($E$2="yearly",$B$2="all"),AND($E$2="yearly",$B$2="selfi")),Data!$E$15,IF(OR(AND($E$2=2008,$B$2="all"),AND($E$2=2008,$B$2="selfi")),"",IF(OR(AND($E$2=2009,$B$2="all"),AND($E$2=2009,$B$2="selfi")),Data!$D$16,IF(OR(AND($E$2=2010,$B$2="all"),AND($E$2=2010,$B$2="selfi")),Data!$D$28,IF(OR(AND($E$2=2011,$B$2="all"),AND($E$2=2011,$B$2="selfi")),Data!$D$40,IF(OR(AND($E$2=2012,$B$2="all"),AND($E$2=2012,$B$2="selfi")),Data!$D$52,IF(OR(AND($E$2=2013,$B$2="all"),AND($E$2=2013,$B$2="selfi")),Data!$D$64,IF(OR(AND($E$2=2014,$B$2="all"),AND($E$2=2014,$B$2="selfi")),Data!$D$76,IF(OR(AND($E$2=2015,$B$2="all"),AND($E$2=2015,$B$2="selfi")),Data!$D$88,IF(OR(AND($E$2=2016,$B$2="all"),AND($E$2=2016,$B$2="selfi")),Data!$D$100,IF(OR(AND($E$2=2017,$B$2="all"),AND($E$2=2017,$B$2="selfi")),Data!$D$112,IF(OR(AND($E$2=2018,$B$2="all"),AND($E$2=2018,$B$2="selfi")),Data!$D$124,IF(OR(AND($E$2=2019,$B$2="all"),AND($E$2=2019,$B$2="selfi")),Data!$D$136,IF(OR(AND($E$2=2020,$B$2="all"),AND($E$2=2020,$B$2="selfi")),Data!$D$148,IF(OR(AND($E$2=2021,$B$2="all"),AND($E$2=2021,$B$2="selfi")),Data!$D$160,IF(OR(AND($E$2=2022,$B$2="all"),AND($E$2=2022,$B$2="selfi")),Data!$D$172,IF(OR(AND($E$2=2023,$B$2="all"),AND($E$2=2023,$B$2="selfi")),Data!$D$184,IF(OR(AND($E$2=2024,$B$2="all"),AND($E$2=2024,$B$2="selfi")),Data!$D$196,IF(OR(AND($E$2=2025,$B$2="all"),AND($E$2=2025,$B$2="selfi")),Data!$D$208,"")))))))))))))))))))
ALLSelect the PropertyYearlySelect a period
25-03-20​
INCOMETOTAL20082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030
Selfi₹ 1,350,900.38₹ 0.00₹ 72,600.00₹ 72,600.00₹ 72,600.00₹ 72,600.00₹ 99,150.00₹ 112,050.00₹ 117,652.50₹ 132,697.88₹ 181,500.00₹ 198,000.00₹ 201,300.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

KRice

Board Regular
Joined
Dec 9, 2003
Messages
171
Office Version
2019
Platform
Windows
I have some questions and some ideas that might address your needs. It appears that B2 assumes a value of either "All" or "Selfi", and nothing else...is this correct? If so, then the OR construction can probably be eliminated. It also appears that E2 assumes a value of either "yearly" or an actual year, and nothing else...is this correct?
Columns B and E have two columns between them. Your example shows "ALL" in the 1st column. Is this supposed to represent column B? And then "Yearly" is shown in the 3rd column. Is this supposed to represent column E, even though there is only one column separating the two entries?

I don't know if you're using data validation to control/facilitate inputs in B2 and E2, but I would recommend that. I've done that in this example, and inserted a blank column to position the B2 and E2 inputs that seem to correspond to your formula. B2 is a simple hardwired list done directly in the data validation window. E2 relies on a helper/lookup list, constructed somewhere else to help organize your yearly updates. That helper table is shown below.
MrExcel_20200325.xlsx
AEAFAG
1SheetRef
2YearData!$D$Reference
3YearlyData!$E$15
42008
5200916Data!$D$16
6201028Data!$D$28
7201140Data!$D$40
8201252Data!$D$52
9201364Data!$D$64
10201476Data!$D$76
11201588Data!$D$88
122016100Data!$D$100
132017112Data!$D$112
142018124Data!$D$124
152019136Data!$D$136
162020148Data!$D$148
172021160Data!$D$160
182022172Data!$D$172
192023184Data!$D$184
202024196Data!$D$196
212025208Data!$D$208
222026220Data!$D$220
232027232Data!$D$232
242028244Data!$D$244
252029256Data!$D$256
262030268Data!$D$268
Sheet2
Cell Formulas
RangeFormula
AG5:AG26AG5=CONCATENATE($AF$2,AF5)


The lengthy formula you presented is repeated here with some structure to help understand its construction...incidentally, you can enter a long formula just like this in the Excel formula bar, using line breaks (Alt-Enter) to help organize the formula:

=IF( OR(AND($E$2="yearly",$B$2="all"),AND($E$2="yearly",$B$2="selfi")), Data!$E$15,
IF( OR(AND($E$2=2008,$B$2="all"),AND($E$2=2008,$B$2="selfi")), "",
IF( OR(AND($E$2=2009,$B$2="all"),AND($E$2=2009,$B$2="selfi")), Data!$D$16,
IF( OR(AND($E$2=2010,$B$2="all"),AND($E$2=2010,$B$2="selfi")), Data!$D$28,
IF( OR(AND($E$2=2011,$B$2="all"),AND($E$2=2011,$B$2="selfi")), Data!$D$40,
IF( OR(AND($E$2=2012,$B$2="all"),AND($E$2=2012,$B$2="selfi")), Data!$D$52,
IF( OR(AND($E$2=2013,$B$2="all"),AND($E$2=2013,$B$2="selfi")), Data!$D$64,
IF( OR(AND($E$2=2014,$B$2="all"),AND($E$2=2014,$B$2="selfi")), Data!$D$76,
IF( OR(AND($E$2=2015,$B$2="all"),AND($E$2=2015,$B$2="selfi")), Data!$D$88,
IF( OR(AND($E$2=2016,$B$2="all"),AND($E$2=2016,$B$2="selfi")), Data!$D$100,
IF( OR(AND($E$2=2017,$B$2="all"),AND($E$2=2017,$B$2="selfi")), Data!$D$112,
IF( OR(AND($E$2=2018,$B$2="all"),AND($E$2=2018,$B$2="selfi")), Data!$D$124,
IF( OR(AND($E$2=2019,$B$2="all"),AND($E$2=2019,$B$2="selfi")), Data!$D$136,
IF( OR(AND($E$2=2020,$B$2="all"),AND($E$2=2020,$B$2="selfi")), Data!$D$148,
IF( OR(AND($E$2=2021,$B$2="all"),AND($E$2=2021,$B$2="selfi")), Data!$D$160,
IF( OR(AND($E$2=2022,$B$2="all"),AND($E$2=2022,$B$2="selfi")), Data!$D$172,
IF( OR(AND($E$2=2023,$B$2="all"),AND($E$2=2023,$B$2="selfi")), Data!$D$184,
IF( OR(AND($E$2=2024,$B$2="all"),AND($E$2=2024,$B$2="selfi")), Data!$D$196,
IF( OR(AND($E$2=2025,$B$2="all"),AND($E$2=2025,$B$2="selfi")), Data!$D$208,
"")))))))))))))))))))

Then the lengthy formula can be replaced with this one:
=IFERROR(INDIRECT(INDEX($AG$3:$AG$26,MATCH($E$2,$AE$3:$AE$26,0))),"")

This formula matches the value shown in $E$2 to that shown in the helper/lookup table, and then it pulls the corresponding reference to your Data sheet. That reference alone can then be used within the INDIRECT function to pull the value from your Data sheet. In this example, I constructed a worksheet named data and place the text "2009 data" in cell D16 to illustrate how choosing
2009 in cell E2 results in the formula using the lookup/helper table to extract the right content from the Data worksheet.

MrExcel_20200325.xlsx
BCDEFGHIJ
1
2All<--Select the Property2009<-- Select a period
325-03-2020
4INCOMETOTAL20082009201020112012
5Selfi1,350,900.38072,600.00#####################
6
72009 data
Sheet2
Cell Formulas
RangeFormula
G7G7=IFERROR(INDIRECT(INDEX($AG$3:$AG$26,MATCH($E$2,$AE$3:$AE$26,0))),"")


I'm not sure how this formula is intended to work with the years that you show in a row. The E2 cell selection returns only one value, but the table you presented appears to be aimed at pulling data for all years. Am I missing something?

Have a look and let me know...
 

shamsu203

New Member
Joined
Jun 12, 2014
Messages
33
Hi Tks for ur fast reply. I have not been clear in my requirements any here here are the answers to ur query For ur info the sheet is send to u is part of seven rows showing different names starting from b5 to b11 for eg b5 selfi b6 faraway and so on.to b11. B2 is a drop down list of these names . If I select all in b2 then all the items are shown otherwise the selected name is shown in the respective row (B5:b11) and the other rows are shown blank. i have a vba which hide the unpopulated rows if b2 is selected for a specific name . eg is b2 is selected for selefi thenrows 6 to 11 are hidden .
e2 is also a drop down list which has "yearly "then 2008 to 2030 . in e2 if yearly is selected then the e 4 row onwards will show the year starting from 2008 on wards to 2030 . the column from 5to 11 will show the yearly income. if e2 shows a specific year eg 2019 then depending on the selection in b2 "all monthly total staring from apr to mar is shown for all 7 items again if specific item is selected then only the income for that item is shown rest of the rows being hidden. i am including a copy of the file where e 2 is for 2019 and b2 for all. i also include a sheet showing a specific selection from b2 for the same period . sorry the column have moved to the right
ALL
drop down b5=b11
Select the Property2019Select a period
25-03-20​
if yearly then 2008 is shown the starting year
INCOMETOTALApr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20
Selfi₹ 201,300.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 18,150.00₹ 18,150.00
Major₹ 75,000.00₹ 5,000.00₹ 5,000.00₹ 5,000.00₹ 5,000.00₹ 5,000.00₹ 5,000.00₹ 5,000.00₹ 15,000.00₹ 6,250.00₹ 6,250.00₹ 6,250.00₹ 6,250.00
Global gf₹ 123,000.00₹ 10,000.00₹ 10,000.00₹ 10,000.00₹ 10,000.00₹ 10,000.00₹ 10,000.00₹ 10,000.00₹ 10,000.00₹ 10,000.00₹ 11,000.00₹ 11,000.00₹ 11,000.00
Global 1₹ 71,280.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00₹ 5,940.00
Global 2₹ 56,857.90₹ 4,477.00₹ 4,477.00₹ 4,477.00₹ 4,477.00₹ 4,477.00₹ 4,924.70₹ 4,924.70₹ 4,924.70₹ 4,924.70₹ 4,924.70₹ 4,924.70₹ 4,924.70
Global 3₹ 42,160.00₹ 3,400.00₹ 3,400.00₹ 3,400.00₹ 3,400.00₹ 3,400.00₹ 3,400.00₹ 3,400.00₹ 3,400.00₹ 3,740.00₹ 3,740.00₹ 3,740.00₹ 3,740.00
Capital Mall₹ 240,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00₹ 20,000.00
Total I
SELFISelect the Property2019Select a period
25-03-20​
INCOMETOTALApr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20
selfi₹ 201,300.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 18,150.00₹ 18,150.00
₹ 0.00
Total Income₹ 201,300.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 16,500.00₹ 18,150.00₹ 18,150.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
ncome
₹ 809,597.90₹ 65,317.00₹ 65,317.00₹ 65,317.00₹ 65,317.00₹ 65,317.00₹ 65,764.70₹ 65,764.70₹ 75,764.70₹ 67,354.70₹ 68,354.70₹ 70,004.70₹ 70,004.70₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00

 

KRice

Board Regular
Joined
Dec 9, 2003
Messages
171
Office Version
2019
Platform
Windows
Thanks for the clarification. Some of my assumptions were not correct. When "yearly" is selected in E2, and the range F4:AB4 displays the years, are they calendar years or fiscal years? I'm confused by this because when a year is selected in E2, you show Apr of that year through Mar of the following year...I'm assuming that is due to the definition of your fiscal year, but could you please clarify that...when years are selected, do you want Jan-Dec or Apr-Mar?

And does your Data worksheet resemble this one? I'm attempting to revise your complex formula into a two-way lookup-type formula that will find the relevant values for the year, or month-year combination, and the associated name. This is what led to my question about the year. Further detail about the Data worksheet structure would be helpful.

MrExcel_20200325.xlsx
ABCDEFGHIJ
2SelfiMajorGlobal gfGlobal 1Global 2Global 3Capital Mall
3Begin FYCal YearMonth
420082008Apr
52008May
62008Jun
72008Jul
82008Aug
92008Sep
102008Oct
112008Nov
122008Dec
132009Jan
142009Feb
152009Mar
1620092009Apr1500
172009May2000
182009Jun2500
192009Jul3000
202009Aug3500
212009Sep4000
222009Oct4500
232009Nov5000
242009Dec5500
252010Jan6000
262010Feb6500
272010Mar7000
2820102010Apr1750
292010May2250
302010Jun2750
312010Jul3250
322010Aug3750
332010Sep4250
342010Oct4750
352010Nov5250
362010Dec5750
372011Jan6250
382011Feb6750
392011Mar7250
4020112011Apr
412011May
422011Jun
Data
Cell Formulas
RangeFormula
B13:B42B13=IF(MOD(ROW()-ROW($B$13),12)=0,B12+1,B12)
 

shamsu203

New Member
Joined
Jun 12, 2014
Messages
33

Hi the dates shown are the fiscal year which starts from apr . I am attaching a copy of the data sheet for ur information .the spreadsheet the data sheet includes all the properties shown i the earlier sheets and automatically calculates the rent rent increase on the specified period the formula used.=IF(B136>$B$5,0,IF(A136>=$D$6,IF(MOD(A136-$D$6,$D$7)=0,FV($D$5,INT((A136-$D$6)/$D$7)+1,0,-C136),FV($D$5,INT((A136-$D$6)/$D$7)+1,0,-C136)),C136)). if the tenant detail changes then the rental rate lease start date first payment date % and increase at period is changed the formula up to the new date is changed by copy and paste function.
MenuLease Term
96​
Lease Term
96​
Lease Term
96​
Lease Term
96​
Lease Term
96​
Lease Term
96​
96​
Rental Rate
₹ 16,500.00​
Rental Rate
₹ 5,000.00​
Rental Rate
₹ 10,000.00​
Rental Rate
₹ 5,400.00​
Rental Rate
₹ 4,070.00​
Rental Rate
₹ 3,400.00​
₹ 20,000.00​
Lease Date
15-02-19​
Lease Date
01-12-15​
Lease Date
01-01-17​
Lease Date
01-01-16​
Lease Date
01-09-13​
Lease Date
01-12-16​
01-07-12​
First Payment Date
15-02-19​
First Pay Date
01-12-15​
First Pay Date
01-01-17​
First Pay Date
01-01-16​
First Pay Date
01-09-13​
First Pay Date
01-12-16​
01-07-12​
Current Date
26-03-20​
Rent Increase %
10.00%​
Rent Increase %
25.00%​
Rent Increase %
10.00%​
Rent Increase %
10.00%​
Rent Increase %
10.00%​
Rent Increase %
10.00%​
0.00%​
Increase @ Period
134​
Increase @ Period
132​
Increase @ Period
133​
Increase @ Period
121​
Increase @ Period
93​
Increase @ Period
132​
250​
Lease Term (no months
12​
SELFIIncrease Interval
48​
MAJORIncrease Interval
36​
GLOBAL GFIncrease Interval
36​
GLOBAL 1Increase Interval
36​
GLOBAL 2Increase Interval
36​
GLOBAL 3
1​
Capital
Increase Due
15-02-20​
01-12-22​
01-01-23​
01-01-22​
01-09-22​
01-12-22​
00-01-00​
New Rent
₹ 19,965.00​
₹ 7,812.50​
₹ 12,100.00​
₹ 6,534.00​
₹ 5,417.17​
₹ 4,114.00​
₹ 20,000.00​
Period No MonthRentRent IncreaseRentRent IncreaseRentRent Increase RentRent IncreaseRentRent IncreaseRentRent IncreaseRent Increase
01-01-09​
Montly RateMontly RateMontly RateMontly RateMontly RateMontly RateMontly Rate
01-01-09​
₹ 0.00
1​
01-01-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
2​
01-02-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
3​
01-03-09​
₹ 6,050.00
₹ 18,150.00​
₹ 1,500.00
₹ 4,500.00​
₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
4​
01-04-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
5​
01-05-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
6​
01-06-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
7​
01-07-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
8​
01-08-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
9​
01-09-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
10​
01-10-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
11​
01-11-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
12​
01-12-09​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00
13​
01-01-10​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 4,700.00₹ 0.00₹ 0.00₹ 0.00
14​
01-02-10​
₹ 6,050.00₹ 1,500.00₹ 0.00₹ 4,700.00₹ 0.00₹ 0.00₹ 0.00
15​
01-03-10​
₹ 6,050.00
₹ 72,600.00​
₹ 1,500.00
₹ 18,000.00​
₹ 0.00
₹ 0.00​
₹ 4,700.00
₹ 14,100.00​
₹ 0.00
₹ 0.00​
₹ 0.00
₹ 0.00​
₹ 0.00
₹ 0.00​
16​
01-04-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
17​
01-05-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
18​
01-06-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
19​
01-07-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
20​
01-08-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
21​
01-09-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
22​
01-10-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
23​
01-11-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
24​
01-12-10​
₹ 6,050.00₹ 1,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
₹ 0.00​
25​
01-01-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
26​
01-02-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
27​
01-03-11​
₹ 6,050.00
₹ 72,600.00​
₹ 2,500.00
₹ 21,000.00​
₹ 6,200.00
₹ 74,400.00​
₹ 4,700.00
₹ 56,400.00​
₹ 3,700.00
₹ 44,400.00​
₹ 2,850.00
₹ 34,200.00​
₹ 0.00
₹ 0.00​
28​
01-04-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
29​
01-05-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
30​
01-06-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
31​
01-07-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
32​
01-08-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
33​
01-09-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
34​
01-10-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
35​
01-11-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
36​
01-12-11​
₹ 6,050.00₹ 2,500.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
₹ 0.00​
37​
01-01-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
38​
01-02-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
39​
01-03-12​
₹ 6,050.00
₹ 72,600.00​
₹ 3,750.00
₹ 33,750.00​
₹ 6,200.00
₹ 74,400.00​
₹ 4,700.00
₹ 56,400.00​
₹ 3,700.00
₹ 44,400.00​
₹ 2,850.00
₹ 34,200.00​
₹ 0.00
₹ 0.00​
40​
01-04-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
41​
01-05-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
42​
01-06-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 0.00
43​
01-07-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
44​
01-08-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
45​
01-09-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
46​
01-10-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
47​
01-11-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
48​
01-12-12​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 4,700.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
49​
01-01-13​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 5,000.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
50​
01-02-13​
₹ 6,050.00₹ 3,750.00₹ 6,200.00₹ 5,000.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
51​
01-03-13​
₹ 6,050.00
₹ 72,600.00​
₹ 3,750.00
₹ 45,000.00​
₹ 6,200.00
₹ 74,400.00​
₹ 5,000.00
₹ 57,300.00​
₹ 3,700.00
₹ 44,400.00​
₹ 2,850.00
₹ 34,200.00​
₹ 20,000.00
₹ 180,000.00​
52​
01-04-13​
₹ 6,050.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
53​
01-05-13​
₹ 6,050.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
54​
01-06-13​
₹ 6,050.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
55​
01-07-13​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
56​
01-08-13​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 3,700.00₹ 2,850.00₹ 20,000.00
57​
01-09-13​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 2,850.00₹ 20,000.00
58​
01-10-13​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 2,850.00₹ 20,000.00
59​
01-11-13​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 2,850.00₹ 20,000.00
60​
01-12-13​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 2,850.00₹ 20,000.00
61​
01-01-14​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
62​
01-02-14​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
63​
01-03-14​
₹ 9,000.00
₹ 99,150.00​
₹ 3,750.00
₹ 45,000.00​
₹ 6,820.00
₹ 81,840.00​
₹ 5,000.00
₹ 60,000.00​
₹ 4,070.00
₹ 46,990.00​
₹ 3,135.00
₹ 35,055.00​
₹ 20,000.00
₹ 240,000.00​
64​
01-04-14​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
65​
01-05-14​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
66​
01-06-14​
₹ 9,000.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
67​
01-07-14​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
68​
01-08-14​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
69​
01-09-14​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
70​
01-10-14​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
71​
01-11-14​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
72​
01-12-14​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
73​
01-01-15​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
74​
01-02-15​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
75​
01-03-15​
₹ 9,450.00
₹ 112,050.00​
₹ 3,750.00
₹ 45,000.00​
₹ 6,820.00
₹ 81,840.00​
₹ 5,000.00
₹ 60,000.00​
₹ 4,070.00
₹ 48,840.00​
₹ 3,135.00
₹ 37,620.00​
₹ 20,000.00
₹ 240,000.00​
76​
01-04-15​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
77​
01-05-15​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
78​
01-06-15​
₹ 9,450.00₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
79​
01-07-15​
₹ 9,922.50₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
80​
01-08-15​
₹ 9,922.50₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
81​
01-09-15​
₹ 9,922.50₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
82​
01-10-15​
₹ 9,922.50₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
83​
01-11-15​
₹ 9,922.50₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
84​
01-12-15​
₹ 9,922.50₹ 3,750.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
85​
01-01-16​
₹ 9,922.50₹ 5,000.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
86​
01-02-16​
₹ 9,922.50₹ 5,000.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
87​
01-03-16​
₹ 9,922.50
₹ 117,652.50​
₹ 5,000.00
₹ 48,750.00​
₹ 6,820.00
₹ 81,840.00​
₹ 5,000.00
₹ 60,000.00​
₹ 4,070.00
₹ 48,840.00​
₹ 3,135.00
₹ 37,620.00​
₹ 20,000.00
₹ 240,000.00​
88​
01-04-16​
₹ 9,922.50₹ 5,000.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
89​
01-05-16​
₹ 9,922.50₹ 5,000.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
90​
01-06-16​
₹ 9,922.50₹ 5,000.00₹ 6,820.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
91​
01-07-16​
₹ 10,418.63₹ 5,000.00₹ 7,200.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
92​
01-08-16​
₹ 10,418.63₹ 5,000.00₹ 7,200.00₹ 5,000.00₹ 4,070.00₹ 3,135.00₹ 20,000.00
93​
01-09-16​
₹ 10,418.63₹ 5,000.00₹ 7,200.00₹ 5,000.00₹ 4,477.00₹ 3,135.00₹ 20,000.00
94​
01-10-16​
₹ 10,418.63₹ 5,000.00₹ 7,200.00₹ 5,000.00₹ 4,477.00₹ 3,135.00₹ 20,000.00
95​
01-11-16​
₹ 10,418.63₹ 5,000.00₹ 7,200.00₹ 5,000.00₹ 4,477.00₹ 3,135.00₹ 20,000.00
96​
01-12-16​
₹ 10,418.63₹ 5,000.00₹ 10,000.00₹ 5,000.00₹ 4,477.00₹ 3,135.00₹ 20,000.00
97​
01-01-17​
₹ 10,418.63₹ 5,000.00₹ 10,000.00₹ 5,000.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
98​
01-02-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,000.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
99​
01-03-17​
₹ 15,000.00
₹ 132,697.88​
₹ 5,000.00
₹ 60,000.00​
₹ 10,000.00
₹ 96,460.00​
₹ 5,000.00
₹ 60,000.00​
₹ 4,477.00
₹ 51,689.00​
₹ 3,400.00
₹ 38,415.00​
₹ 20,000.00
₹ 240,000.00​
100​
01-04-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,000.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
101​
01-05-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,000.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
102​
01-06-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,000.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
103​
01-07-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
104​
01-08-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
105​
01-09-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
106​
01-10-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
107​
01-11-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
108​
01-12-17​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
109​
01-01-18​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
110​
01-02-18​
₹ 15,000.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
111​
01-03-18​
₹ 16,500.00
₹ 181,500.00​
₹ 5,000.00
₹ 60,000.00​
₹ 10,000.00
₹ 120,000.00​
₹ 5,400.00
₹ 63,600.00​
₹ 4,477.00
₹ 53,724.00​
₹ 3,400.00
₹ 40,800.00​
₹ 20,000.00
₹ 240,000.00​
112​
01-04-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
113​
01-05-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
114​
01-06-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
115​
01-07-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
116​
01-08-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
117​
01-09-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
118​
01-10-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
119​
01-11-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
120​
01-12-18​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,400.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
121​
01-01-19​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
122​
01-02-19​
₹ 0.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
123​
01-03-19​
₹ 16,500.00​
₹ 16,500.00
₹ 198,000.00​
₹ 5,000.00
₹ 60,000.00​
₹ 10,000.00
₹ 120,000.00​
₹ 5,940.00
₹ 66,420.00​
₹ 4,477.00
₹ 53,724.00​
₹ 3,400.00
₹ 40,800.00​
₹ 20,000.00
₹ 240,000.00​
124​
01-04-19​
₹ 16,500.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
125​
01-05-19​
₹ 16,500.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
126​
01-06-19​
₹ 16,500.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
127​
01-07-19​
₹ 16,500.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
128​
01-08-19​
₹ 16,500.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,477.00₹ 3,400.00₹ 20,000.00
129​
01-09-19​
₹ 16,500.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,924.70₹ 3,400.00₹ 20,000.00
130​
01-10-19​
₹ 16,500.00​
₹ 16,500.00₹ 5,000.00₹ 10,000.00₹ 5,940.00₹ 4,924.70₹ 3,400.00₹ 20,000.00
131​
01-11-19​
₹ 16,500.00​
₹ 16,500.00₹ 15,000.00₹ 10,000.00₹ 5,940.00₹ 4,924.70₹ 3,400.00₹ 20,000.00
132​
01-12-19​
₹ 16,500.00​
₹ 16,500.00₹ 6,250.00₹ 10,000.00₹ 5,940.00₹ 4,924.70₹ 3,740.00₹ 20,000.00
133​
01-01-20​
₹ 16,500.00​
₹ 16,500.00₹ 6,250.00₹ 11,000.00₹ 5,940.00₹ 4,924.70₹ 3,740.00₹ 20,000.00
134​
01-02-20​
₹ 16,500.00​
₹ 18,150.00₹ 6,250.00₹ 11,000.00₹ 5,940.00₹ 4,924.70₹ 3,740.00₹ 20,000.00
135​
01-03-20​
₹ 16,500.00​
₹ 18,150.00
₹ 201,300.00​
₹ 6,250.00
₹ 75,000.00​
₹ 11,000.00
₹ 123,000.00​
₹ 5,940.00
₹ 71,280.00​
₹ 4,924.70
₹ 56,857.90​
₹ 3,740.00
₹ 42,160.00​
₹ 20,000.00
₹ 240,000.00​
 

KRice

Board Regular
Joined
Dec 9, 2003
Messages
171
Office Version
2019
Platform
Windows
To eliminate the lengthy nested IF statement with the embeded ANDs and ORs that was the subject of your question, I propose a different approach that relies on creating a summary table whose Names at left and whose column headings above can be used to find the relevant information in the data table using a single, simpler formula that does not require any year-to-year updating. I'll explain the details:

A helper table is constructed somewhere to the right of the summary table. This isn't absolutely necessary (and it can be hidden if desired), but it is an easy way to construct the column headings: one set of column headings (i.e, the "Row for Yearly" column in the helper table) is used to display sequential years when "Yearly" is selected in the E2 drop down list, the other set of column headings (i.e, the "Row for Monthly" column in the helper table) is used to display sequential months in the fiscal year when a specific fiscal year is selected in the E2 drop down list. The column headings are inserted with a formula in F4:AB4...that formula relies on the TRANSPOSE function, as well as an IF statement to determine which of the two column headings to insert. When months are shown, the summary table extends only to column Q, so I padded the remainder of the column headings (R4:AB4) with a nonprinting character (CHAR(129)) to clean up the appearance. The portion of the helper/lookup table goes blank if "yearly" is selected.

Based on which column heading is inserted, we would like to identify the place in the Data table corresponding to the column heading (either a specific month when Month-Year is shown, or the beginning of the fiscal year when only the Year is shown). To do that, I added a helper row in small grey font above the columns in F3:AB3, and the formula used in that helper row takes advantage of the fact that you have a Period Number column in your Data table (column A), and cell Data!:$A$13 represents the upper left corner of the income data table, which corresponds to period 1 beginning on 1 January 2009. Using that information, the helper row determines the number of months after 1 Jan 2009 until the payment period of interest for that particular column. This helper row can be hidden, the font lightened, made smaller, changed in color, but it should not be deleted, as the summary table lookups depend on these numbers.

B2 uses Data Validation and relies on the column "Names" in the helper/lookup area. Formulas in B5:B11 then examine the content of B2 and either copy the name from the helper/lookup area or display a blank, depending on whether "All" or a name has been selected from the B2 dropdown list. If this works for you, the VBA code performing this hiding function isn't necessary, as the formula does that.

Your data table has a consistent structure, and we know where to find the names (Data!$A$7:$V$7) and the payment period numbers (Data!$A$13:$A$276)---the last point corresponding to payment period 264 on 1 December 2030---and we've already identified the payment period number of interest for each column in the summary table, so we can use a single formula to obtain the relevant information from the data table:

=IFERROR(SUM(OFFSET(Data!$A$13,F$3-1,MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="YEARLY",12,1),1)),"")

This formula uses the OFFSET function, essentailly using the upper left corner of your data table as a common reference point, and then we determine how many columns to move to the right to find the monthly data corresponding to the name (found using the MATCH function). We know how many rows to move down from the reference point (given by the helper row F3:AB3). You will find an IF statement in this formula, and it is used to either select 1 cell or 12 cells starting at the prescribed location to feed into the SUM function, because we want either a single month or an entire fiscal year reported back to the SUM function. Finally, the entire formula is wrapped in an IFERROR function to display a blank if an error is encounted, such as would occur if a match is not found in a given cell.

This single formula is copied throughout the table, and once it is confirmed to pick up the correct values from the data table, it should require no further maintenance until you need to adjust the range limts after year 2030, or if you add other names to the data table (in which case, the helper table and other formulas would need some light attention to ensure that the expanded ranges were accounted for).

I noticed a couple of things while confirming functionality: 1) check payment period 131 for Selfi where that payment stands out because it is much different than others before and after it...I didn't know if that was a typo; and 2) check the sum of fiscal year 2016 for Selfi...in your first post, it shows a value ending in 88 while the sum I show here ends in 91...a minor difference, but the approach I describe here computes the sum for 12 months rather than pulling a pre-summed number from the data table.

Click on the clipboard icon in the XL2BB spreadsheet rendering here to copy this to your clipboard for easy pasting into Excel to try it out. The icon is found at the upper left of the sheet at the intersection of column and row headings.

MrExcel_20200325.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Income SummaryHelper Table:
2Selfi<-Select PropertyYearly<- Select FYCol forRow forRow for
3Current Date2020-03-26Period Ref# ->416284052647688100112124136148160172184196208220232244256NamesYearlyMonthly
4Row Total20082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030All2008
5Name 1 ->Selfi₹ 1,332,750.41₹ 72,600.00₹ 72,600.00₹ 72,600.00₹ 72,600.00₹ 99,150.00₹ 112,050.00₹ 117,652.50₹ 132,697.91₹ 181,500.00₹ 198,000.00₹ 201,300.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00₹ 0.00Selfi2009
6Name 2 ->Major2010
7Name 3 ->Global GF2011
8Name 4 ->Global 12012
9Name 5 ->Global 22013
10Name 6 ->Global 32014
11Name 7 ->Capital2015
12Total Income₹ 1,332,750.41₹ 72,600.00₹ 72,600.00₹ 72,600.00₹ 72,600.00₹ 99,150.00₹ 112,050.00₹ 117,652.50₹ 132,697.91₹ 181,500.00₹ 198,000.00₹ 201,300.002016
132017
142018
152019
162020
172021
182022
192023
202024
212025
222026
232027
242028
252029
262030
Sheet2
Cell Formulas
RangeFormula
B3B3=TODAY()
F3:AB3F3=IFERROR(DATEDIF(DATE(2009,1,1),IF(ISNUMBER(F4),DATE(F4,4,1),DATE(YEAR(F4),MONTH(F4),1)),"m")+1,"")
F4:AB4F4=IF($E$2="Yearly",TRANSPOSE(AE4:AE26),TRANSPOSE(AF4:AF26))
F5:AB11F5=IFERROR(SUM(OFFSET(Data!$A$13,F$3-1,MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="YEARLY",12,1),1)),"")
F12:AB12F12=IF(SUM(F5:F11)<>0,SUM(F5:F11),"")
B5:B11B5=IF(OR($B$2="all",$B$2=AD5),AD5,"")
C5:C12C5=IF(SUM(F5:AB5)<>0,SUM(F5:AB5),"")
AF4AF4=IFERROR(TEXT(DATE($E$2,4,15),"mmm-yyyy"),"")
AF5AF5=IFERROR(TEXT(DATE($E$2,5,15),"mmm-yyyy"),"")
AF6AF6=IFERROR(TEXT(DATE($E$2,6,15),"mmm-yyyy"),"")
AF7AF7=IFERROR(TEXT(DATE($E$2,7,15),"mmm-yyyy"),"")
AF8AF8=IFERROR(TEXT(DATE($E$2,8,15),"mmm-yyyy"),"")
AF9AF9=IFERROR(TEXT(DATE($E$2,9,15),"mmm-yyyy"),"")
AF10AF10=IFERROR(TEXT(DATE($E$2,10,15),"mmm-yyyy"),"")
AF11AF11=IFERROR(TEXT(DATE($E$2,11,15),"mmm-yyyy"),"")
AF12AF12=IFERROR(TEXT(DATE($E$2,12,15),"mmm-yyyy"),"")
AF13AF13=IFERROR(TEXT(DATE($E$2+1,1,15),"mmm-yyyy"),"")
AF14AF14=IFERROR(TEXT(DATE($E$2+1,2,15),"mmm-yyyy"),"")
AF15AF15=IFERROR(TEXT(DATE($E$2+1,3,15),"mmm-yyyy"),"")
AF16:AF26AF16=CHAR(129)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

shamsu203

New Member
Joined
Jun 12, 2014
Messages
33
Hi Wowl", great it works fine but for the following
1) B2 ALL E2 yearly the following not shown in 2008 Selfi Major (simlilary if u select slefi or major alone
2) b2 all e 2 yearly Capital not shown not yrarly wise nor for each year.
u asked to use the array function is it necessary and how it benefits I have not used array function up to now.
Once again very much appreciated It has been a great learning exp for me tks once again looking forward to ur reply
bi
 

KRice

Board Regular
Joined
Dec 9, 2003
Messages
171
Office Version
2019
Platform
Windows
Good...almost there.
For #1, I see that B2=All, E2=2008 shows income details for periods 1, 2, and 3 (Jan-Mar 2009) at the end of fiscal year 2008 for both Selfi and Major...so that part is working correctly. And 0's are shown for the other names, consistent with the Data table. However, as you point out, upon making a change so that E2=Yearly, the 2008 information is missing for both Selfi and Major. And now that I look into it, I see why. The helper row that has been placed above the column headers is blank for year 2008, so the OFFSET formula that uses the helper row as a coordinate for finding information in the Data table gives an error (because no data table row coordinate is being provided). This is because the helper row was constructed to find the beginning of the fiscal year, and the beginning of FY2008 is not shown in the Data table. Rather than change the approach here completely, I made a few changes to the main formula in the summary table. This revised formula is placed in F5 and then copied throughout the body of the table:
=IFERROR(IF(AND($E$2<>"Yearly",F$3=""),"",SUM(OFFSET(Data!$A$13,IF(F$3="",0,F$3-1),MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="Yearly",IF(F$3="",G$3-1,12),1),1))),"")

Within this formula, you will see:
IF(AND($E$2<>"Yearly",F$3=""),""
This part checks if the helper row entry is blank and a specific year was selected in E2. When this condition is TRUE, that means a specific month-year period does not exist in the Data table, so we insert a blank ("") for the result. When the condition is FALSE, then that means there are three potential scenarios---either F3 has a value (and E2 is either Yearly or a specific year), or F3 is blank and E2 is Yearly, as shown here:
a. E2=Yearly , F3 is blank
b. E2=Yearly , F3 contains some value
c. E2=some year, F3 contains some value

We move to the second part of the formula to address these remaining scenarios. The last version of the formula adequately addressed scenarios "b" and "c", those scenarios where F3 held a value. But to address scenario "a" a few adjustments to the last version of the formula are necessary. The row argument in OFFSET function is now:
IF(F$3="",0,F$3-1)
This checks to see if the helper row has a blank, which will occur if there is no Period Number entry in the Data table that corresponds to the beginning of the fiscal year. This is what caused the problem you identified. If F3 has a value, we use the row argument previously specified, but if F3 is blank, we set the row argument to 0...at the top of the data table.

Then we need to ensure that the correct number of rows are being selected. In the earlier formula, the expression
IF($E$2="YEARLY",12,1)
returned either 12 rows or 1 row, depending on whether E2=Yearly. This needs some revision to account for the situation where we need less than 12 months at the top of the data table. So some nested IF statements can be used:
IF($E$2="Yearly",IF(F$3="",G$3-1,12),1)
and this sets the number of rows to return as 1 less than the helper row value in the column to the right. This will pick up the incomplete fiscal years at the top of the Data table.

For #2, check your Data sheet and confirm that the name cell is "Capital" and then check the helper table and confirm that the list also includes "Capital". In an earlier post, you showed "Capital Mall" in the summary table. When I put together the summary table, I noticed this and changed the helper table entry to "Capital". Pick whichever you prefer, but be sure that the helper table entries and the names in the Data table match exactly.

Your question about the array formula has two answers:
1) If the TRANSPOSE functions are used (they are in the current implementation), then, yes, an array formula is required because TRANSPOSE depends on that.
2) There are other ways to construct the column headings without using TRANSPOSE, and some of those options would not require an array formula. For example the listed items and formulas in the two columns of the helper table could be incorporated in formulas running across the column header row of the summary table, and an IF statement could specify which to display...year or month-year.

I'm including only a portion of the summary table because only one formula has changed. Give this a try and let me know if it helps...

Cell Formulas
RangeFormula
B3B3=TODAY()
F3:G3F3=IFERROR(DATEDIF(DATE(2009,1,1),IF(ISNUMBER(F4),DATE(F4,4,1),DATE(YEAR(F4),MONTH(F4),1)),"m")+1,"")
F4:G4F4=IF($E$2="Yearly",TRANSPOSE(AE4:AE26),TRANSPOSE(AF4:AF26))
F5:G11F5=IFERROR(IF(AND($E$2<>"Yearly",F$3=""),"",SUM(OFFSET(Data!$A$13,IF(F$3="",0,F$3-1),MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="Yearly",IF(F$3="",G$3-1,12),1),1))),"")
F12:G12F12=IF(SUM(F5:F11)<>0,SUM(F5:F11),"")
B5:B11B5=IF(OR($B$2="all",$B$2=AD5),AD5,"")
C5:C12C5=IF(SUM(F5:AB5)<>0,SUM(F5:AB5),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

shamsu203

New Member
Joined
Jun 12, 2014
Messages
33
Hi
I changed as suggested it works fine except for the Capital wihch same both in Data and Summary worksheet. I would like to avoid the array functions as I have no knowledge of it.
tks once again
shamsu
 

Forum statistics

Threads
1,089,220
Messages
5,406,929
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top