Formula to Display Sheet Name of Worksheet that Contains Max Value Across Multiple Sheets in Workbook

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
I have created a workbook that is comparing my business transactions from year to year. Each year is represented by a worksheet (2020, 2019, 2018, 2017, etc.). On each worksheet, I have listed the transactions, and then broken down data for the year including number of transactions, total volume, total commission, average sales price, largest transaction, smallest transaction, average # days of a transaction, etc.

I created an "Overall" sheet for the beginning of the workbook to summarize my whole business. I have been able to figure out the formula for the yearly average, the year that had the highest and the year with the lowest of each statistic. But I for the max and the min, I would like that sheet to display which year (or sheet) those stats came from. I can't figure out a formula to display that? I have spent weeks searching the internet for this formula. PLEASE HELP!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
@Dave Patton - Honestly I don't understand how to use your formulas. I am sorry.
@alz - How is this -

Transaction_Comparison_ELO.xlsx
ABCD
12Avg Sales Price $ 403,481
13Best Average Sales Price$ 527,0292016
14Lowest Average Sales Price$ 294,5002014
15
16Largest Transaction$ 1,425,000.00#VALUE!2020
17Smallest Transaction$ 45,200.002013
18
19Avg Transactions / Year23.25
20Highest Transations 412020
21Least Transactions152014
22
23Avg Sales Volume / Year$ 9,380,925
24Highest Sales Volume$ 17,076,5632020
25Least Sales Volume$ 4,417,5002014
26
27Total # of Buyers87
28Avg # of Buyers / Year10.88
29Avg % of Buyers47%
30Highest # of Buyers2020182020
31Lowest # of Buyers620192016
32
33Total # of Sellers99
34Avg # of Sellers / Year12.38
35% of Sellers53%
36Highest # of Sellers2120192020
37Lowest # of Sellers720182014
Overall
Cell Formulas
RangeFormula
B12B12=C9/B9
B13B13=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)
C13C13=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))))-1)
B14B14=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)
C14C14=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60)=MIN('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60),{1,2,3,4,5,6,7,8}))))-1)
B16B16=MAX('2021'!B69,'2020'!B69,'2019'!B69,'2018'!B69,'2017'!B69,'2016'!B69,'2015'!B69,'2014'!B69,'2013'!B69)
C16C16=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69)=MAX('2020'!HB69,'2019'!HB69,'2018'!HB69,'2017'!HB69,'2016'!HB69,'2015'!HB69,'2014'!HB69,'2013'!HB69),{1,2,3,4,5,6,7,8}))))-1)
B17B17=MIN('2021'!B70,'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)
C17C17=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70)=MIN('2020'!B70,'2019'!B70,'2018'!B70,'2017'!B70,'2016'!B70,'2015'!B70,'2014'!B70,'2013'!B70),{1,2,3,4,5,6,7,8}))))-1)
B19B19=(B9/DATEDIF(M1,TODAY(),"Y"))
B20B20=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)
C20C20=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MAX('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))))-1)
B21B21=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)
C21C21=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61)=MIN('2020'!B61,'2019'!B61,'2018'!B61,'2017'!B61,'2016'!B61,'2015'!B61,'2014'!B61,'2013'!B61),{1,2,3,4,5,6,7,8}))))-1)
B23B23=(C9/DATEDIF(M1,TODAY(),"Y"))
B24B24=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)
C24C24=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MAX('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))))-1)
B25B25=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)
C25C25=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62)=MIN('2020'!B62,'2019'!B62,'2018'!B62,'2017'!B62,'2016'!B62,'2015'!B62,'2014'!B62,'2013'!B62),{1,2,3,4,5,6,7,8}))))-1)
B27B27=(SUM('2021'!C63+'2020'!C63+'2019'!C63+'2018'!C63+'2017'!C63+'2016'!C63+'2015'!C63+'2014'!C63+'2013'!C63))
B28B28=B27/DATEDIF(M1,TODAY(),"Y")
B29B29=B27/B9
B30B30=MAX('2020'!C63,'2019'!C63,'2018'!C63,'2017'!C63,'2016'!C63,'2015'!C63,'2014'!C63,'2013'!C63)
C30C30=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MAX('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))))-1)
B31B31=MIN('2020'!C63,'2019'!C63,'2018'!C63,'2017'!C63,'2016'!C63,'2015'!C63,'2014'!C63,'2013'!C63)
C31C31=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63)=MIN('2020'!B63,'2019'!B63,'2018'!B63,'2017'!B63,'2016'!B63,'2015'!B63,'2014'!B63,'2013'!B63),{1,2,3,4,5,6,7,8}))))-1)
B33B33=(SUM('2021'!C64+'2020'!C64+'2019'!C64+'2018'!C64+'2017'!C64+'2016'!C64+'2015'!C64+'2014'!C64+'2013'!C64))
B34B34=B33/DATEDIF(M1,TODAY(),"Y")
B35B35=B33/B9
B36B36=MAX('2020'!C64,'2019'!C64,'2018'!C64,'2017'!C64,'2016'!C64,'2015'!C64,'2014'!C64,'2013'!C64)
C36C36=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MAX('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))))-1)
B37B37=MIN('2020'!C64,'2019'!C64,'2018'!C64,'2017'!C64,'2016'!C64,'2015'!C64,'2014'!C64,'2013'!C64)
C37C37=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64)=MIN('2020'!B64,'2019'!B64,'2018'!B64,'2017'!B64,'2016'!B64,'2015'!B64,'2014'!B64,'2013'!B64),{1,2,3,4,5,6,7,8}))))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Transaction_Comparison_ELO.xlsx
ABCDEF
40 Avg # Max #Year of Max Min # Year of Min
411st: 49201822013
422nd: 611202032014
433rd: 51520204#VALUE!
444th: 49202012018
Overall
Cell Formulas
RangeFormula
B41:B44B41=(SUM('2021'!F62,'2020'!F62,'2019'!F62,'2018'!F63,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)/9)
C41:C44C41=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)
D41D41=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MAX('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))))-1)
E41:E44E41=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)
F41F41=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62)=MIN('2020'!F62,'2019'!F62,'2018'!F62,'2017'!F62,'2016'!F62,'2015'!F62,'2014'!F62,'2013'!F62),{1,2,3,4,5,6,7,8}))))-1)
D42D42=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MAX('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))))-1)
F42F42=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63)=MIN('2020'!F63,'2019'!F63,'2018'!F63,'2017'!F63,'2016'!F63,'2015'!F63,'2014'!F63,'2013'!F63),{1,2,3,4,5,6,7,8}))))-1)
D43D43=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MAX('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))))-1)
F43F43=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64)=MIN('2020'!F64,'2019'!F64,'2018'!F64,'2017'!F64,'2016'!F64,'2015'!F64,'2014'!F64,'2013'!F64),{1,2,3,4,5,6,7,8}))))-1)
D44D44=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MAX('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))))-1)
F44F44=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65)=MIN('2020'!F65,'2019'!F65,'2018'!F65,'2017'!F65,'2016'!F65,'2015'!F65,'2014'!F65,'2013'!F65),{1,2,3,4,5,6,7,8}))))-1)
 

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
Cell Formulas
RangeFormula
G41:G44G41=(SUM('2021'!H62,'2020'!H62,'2019'!F62,'2018'!H63,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)/9)
H41:H44H41=MAX('2020'!H62,'2019'!F62,'2018'!H63,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)
I41I41=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MAX('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))))-1)
J41:J44J41=MIN('2020'!H62,'2019'!H62,'2018'!H63,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)
K41K41=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62)=MIN('2020'!H62,'2019'!H62,'2018'!H62,'2017'!H62,'2016'!H62,'2015'!H62,'2014'!H62,'2013'!H62),{1,2,3,4,5,6,7,8}))))-1)
I42I42=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MAX('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))))-1)
K42K42=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63)=MIN('2020'!H63,'2019'!H63,'2018'!H63,'2017'!H63,'2016'!H63,'2015'!H63,'2014'!H63,'2013'!H63),{1,2,3,4,5,6,7,8}))))-1)
I43I43=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MAX('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))))-1)
K43K43=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64)=MIN('2020'!H64,'2019'!H64,'2018'!H64,'2017'!H64,'2016'!H64,'2015'!H64,'2014'!H64,'2013'!H64),{1,2,3,4,5,6,7,8}))))-1)
I44I44=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MAX('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))))-1)
K44K44=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65)=MIN('2020'!H65,'2019'!H65,'2018'!H65,'2017'!H65,'2016'!H65,'2015'!H65,'2014'!H65,'2013'!H65),{1,2,3,4,5,6,7,8}))))-1)
 

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Transaction_Comparison_ELO.xlsx
ABCDEFGHIJK
48Jan1.002#VALUE!0#VALUE!$ 328,222$ 738,000$ -
Overall
Cell Formulas
RangeFormula
B48B48=(SUM('2021'!F69,'2020'!F69,'2019'!F69,'2018'!F69,'2017'!F69,'2016'!F69,'2015'!F69,'2014'!F69,'2013'!F69)/9)
C48C48=MAX('2020'!F69,'2019'!F69,'2018'!F69,'2017'!F69,'2016'!F69,'2015'!F69,'2014'!F69,'2013'!F69)
D48D48=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MAX('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))))-1)
E48E48=MIN('2020'!F69,'2019'!F69,'2018'!F69,'2017'!F69,'2016'!F69,'2015'!F69,'2014'!F69,'2013'!F69)
F48F48=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))))+1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( ",'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69","'",""),",","@",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))),"!","#",SUMPRODUCT(IF(CHOOSE({1,2,3,4,5,6,7,8},'2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69)=MIN('2020'!HF69,'2019'!HF69,'2018'!HF69,'2017'!HF69,'2016'!HF69,'2015'!HF69,'2014'!HF69,'2013'!HF69),{1,2,3,4,5,6,7,8}))))-1)
G48G48=(SUM('2021'!H69,'2020'!H69,'2019'!H69,'2018'!H69,'2017'!H69,'2016'!H69,'2015'!H69,'2014'!H69,'2013'!H69)/9)
H48H48=MAX('2020'!H69,'2019'!H69,'2018'!H69,'2017'!H69,'2016'!H69,'2015'!H69,'2014'!H69,'2013'!H69)
J48J48=MIN('2020'!H69,'2019'!H69,'2018'!H69,'2017'!H69,'2016'!H69,'2015'!H69,'2014'!H69,'2013'!H69)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,625
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are your sheets 2013 ... 2020 in a series or do you have other sheets in that range of sheets?

=MAX('2020'!B60,'2019'!B60,'2018'!B60,'2017'!B60,'2016'!B60,'2015'!B60,'2014'!B60,'2013'!B60) your formula
=MAX('2013:2020'!$B60) was my suggestion Do they yield the same result?
 

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Yes, except 2020 is closest to the overall.
I have added a 2021 but that data isn't complete so I don't want it added yet.
 

Attachments

  • Screen Shot 2021-01-21 at 11.40.32 PM.png
    Screen Shot 2021-01-21 at 11.40.32 PM.png
    55.6 KB · Views: 2

erino

New Member
Joined
Jan 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. MacOS
@Dave Patton - That gives the value, but I want the name of the sheet to appear so I know what year the value is from
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,625
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
to erino you dated "@Dave Patton - That gives the value, but I want the name of the sheet to appear so I know what year the value is from"

Review my initial post and try one of the formula with your list of sheet names and relevant cell address.

- under Sheets list each sheet name 2013 .. 2020 and extend the range from G2:G4 to the range that you use
- edit the range of cells from A1:C4 or just show one cell such as C60
- change C3 to the cell that yields the maximum
- other edits as necessary
Confirm the array for by entering with Control Shift Enter (CSE)

You can name the range of sheet names but that is not necessary. I left that part in the post.

2016 3d.xlsm
BCDEFG
1SummarySheets
2ArrayNot CSE2014
3Maximum 24201420142015
4Minimum1201620162016
5
6ArrayNot CSE
7Maximum 2420142014
8Minimum120162016
Results
Cell Formulas
RangeFormula
C3,C7C3=MAX('2014:2016'!$A$1:$C$4)
D8,D3:D4D3=INDEX($G$2:$G$4,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!A1:C4"),C3)>0,0))
E3E3=INDEX($G$2:$G$4,CEILING(MATCH(1,FREQUENCY(C3,'2014:2016'!$A$1:$C$4),0) / COUNT('2014:2016'!$A$1:$C$4)* COUNTA($G$2:$G$4),1))
C4,C8C4=MIN('2014:2016'!$A$1:$C$4)
E4,E7:E8E4=INDEX(rSheets,CEILING(MATCH(1,FREQUENCY(C4,'2014:2016'!$A$1:$C$4),0) / COUNT('2014:2016'!$A$1:$C$4)* COUNTA(rSheets),1))
D7D7=INDEX(rSheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&rSheets&"'!A1:C4"),C7)>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
rSheets=Results!$G$2:$G$4D3:E4, D7:E8


Your could set up a spreadsheet and test the formulas that I posted.
You could then review the formulas in a working model.
You can enter just a few numbers and review the results.
 
Last edited:

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,625
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I'm sorry. I did not read the full post.

to erino you dated should be stated.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,961
Messages
5,621,842
Members
415,860
Latest member
jacky1984

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
Top