Forum Discussion
Solved
Calculating MODE Across Several Worksheets
Hello. I have data in 11 different cells across several worksheets. For example, I have 20 worksheets in a single workbook and those worksheets all have data in cells D3, D5, D7, D9, D11, D13, D15, D17, D19, D21 and D23. I then have one worksheet with summary statistics for all the other worksheets.
Within the summary worksheet, I need to calculate MODE for each one of those cells across all the worksheets. The simple MODE function does not work.
I'm using Excel 365. Any help would be appreciated!
=MODE(TOCOL(Sheet1:Sheet20!D3))where Sheet1 and Sheet20 are the leftmost and rightmost of the 20 worksheets respectively.
Regards
- Thanks to all of you! The "TOCOL" function is new to me - this is very handy. JosWoolley's resolution is more at my level of expertise and works great. Thank-you! There is one question I do have for JosWooley and I will reply to that post directly. Thanks again everyone!
Given a 3D range 'data', HSTACK will convert sheet data to columns. Modeλ is then applied by row. Finally intervening lines that give rise to errors are filtered out.
= LET( Modeλ, LAMBDA(x, MODE(x)), consolidated, HSTACK(data), modes, BYROW(consolidated, Modeλ), FILTER(modes, ISNUMBER(modes)) )=MODE(DROP(REDUCE("",TOCOL(SEQUENCE(1,4,21,1)),
LAMBDA(a,x,VSTACK(a,INDIRECT("Sheet"&x&"!D17")))),1))For the MODE of cell D17 you can apply this formula. The assumption is that the sheetnames are sheet1, sheet2, sheet3 and so on. Within SEQUENCE(1,4,21,1) you can determine the number of worksheets (4) and the worksheet you want to start with (sheet21 or 21 within SEQUENCE).
Then this formula returns the MODE for cell D17 of sheet21, sheet22, sheet23 and sheet24.
Changes can be made dynamically e.g. SEQUENCE(1,20,1,1) would start with sheet1 and return the MODE for sheet1 to sheet20.
=MODE(TOCOL(Sheet1:Sheet20!D3))where Sheet1 and Sheet20 are the leftmost and rightmost of the 20 worksheets respectively.
Regards
- Thanks JosWoolley! Works great and I have one question, based on the fact that the number of worksheets will be dynamic over time. For other statistics I created code that lists the names of all the worksheets in the workbook, less the summary sheet. To run those other stats I name that list as "All_Worksheets" to deal with the dynamic number of worksheets and refer to it in those formulas using INDIRECT. Can the same be done with TOCOL to capture a dynamic number of worksheets? For example, MODE.MULT(TOCOL(INDIRECT("'"&Summary_Worksheets&"'!D33"))) is not working for me.
Personally I'd prefer to avoid such a set-up, as it requires the volatile INDIRECT. Can you not simply ensure that you have two dummy worksheets, named Start and End for example, which are blank and between which will sit the relevant worksheets? You can then use:
=MODE(TOCOL(Start:End!D3))Failing that, you could use:
=MODE(N(INDIRECT("'"&All_Worksheets&"'!"&CELL("address",D3))))
