When Dynamic Arrays (DA) were introduced, it opened a plethora of possibilities and a lot of formula nerds and automation ninjas were empowered to begin a new wave of creativity while building more powerful worksheets with fewer formulas. As we explore the potential, we are discovering that there is a new limitation that begs for a solution. This is a proposal for a new function that can expand the flexibility of scalar functions that accept arrays as parameters so they can operate more robustly in an array world.

Consider the following problem as an example.

We have a two-dimension table of numeric values – we'll call each number a score. The values are organized by row – one row for each student. Each of 20 students has 10 scores, and we want to find the maximum of the averages of the top five scores for each student. We're going to need to use the LARGE, AVERAGE, and MAX functions.

Without getting into arrays, we create five "helper" columns and, in each, put our LARGE function. For the first student, the formula in the first helper column is:

=LARGE(A1:J1,1)

The second helper column:

=LARGE(A1:J1,2)

And so on. We average these in a sixth column and then get the maximum of the averages for all 20 students. Six additional columns and a single cell dedicated to the maximum of the averages across all students.

We can introduce arrays and cut our six helper columns down to one by coercing the LARGE function to produce an array and passing that array to the AVERAGE function, one formula per student (row):

=AVERAGE(LARGE(A1:J1,{1,2,3,4,5}))

Now we have one helper column and a cell dedicated to the maximum of the averages.

Can we go further in our effort to produce a solution with a minimal number of formulas? Given the current state of things, that is as simple as I can go. There may be some magic out there that can keep going but I'm at a loss at this point.

The problem with going further with arrays is we are bound by the inability of Excel to know that if we pass our entire array containing all students and their scores to LARGE, we want it to process each student separately and generate one result per student. Or, in this case, one result per student and for each of the five ranks we passed to it. This is because the LARGE function along with many other functions that accept arrays as parameters handle the arrays internally. Whereas any function that accepts a scalar value can be coerced to produce an array – not by itself, but by Excel creating multiple invocations of that function, one for each element in the array, and then assembling the results in a result array.

But what if we had an explicit way to tell Excel to process each row in an array as a separate invocation of that function just as it already knows to do when we pass an array where a scalar parameter is expected?

Consider a new function: SEGMENT. The purpose of the function is to instruct Excel to take a two-dimension array and process it by row or by column, just as it knows how to do with our second array we are passing to the LARGE function for the "k" parameter to generate the five largest values as an array.

SEGMENT(Array, [ByColumn])

    Array – any two-dimension array.

    ByColumn – Optional. Pass TRUE to segment the array by columns. Default is FALSE which results in the array segmented by rows.

In our LARGE example, we can then generate one array of all top five scores for all 20 students:

=LARGE(SEGMENT(A1:J20),{1,2,3,4,5})

The result would be 20 rows by 5 columns where each row is a single student, and each column is one of the five ranked values. The next step is to average each student's top five scores. We apply the SEGMENT function again and pass that result to the AVERAGE function:

=AVERAGE(SEGMENT(LARGE(SEGMENT(A1:J20),{1,2,3,4,5})))

The result is a single dimension array of 20 averages, one element per student. Ultimately, we want the maximum average across all students, so we pass this array to MAX:

=MAX(AVERAGE(SEGMENT(LARGE(SEGMENT(A1:J10),{1,2,3,4,5}))))

And we have our scalar answer with one formula.

I suppose we could add an additional parameter to SEGMENT to tell it how many rows or columns constitute a single element in its result but that may be overkill. Another parameter might be the specific rows or columns to segment. Yet another option would be to be able to specify overlapping ranges for a moving average function (pass a single dimension range and a size of each segment.)

As a final note, there are several posts in this forum for more array aware functions such as here:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38952709-sumcols-and-sumrows-functions-for-dynamic-arrays

I am pretty sure the SEGMENT function would be a solution for the many new functions that are being proposed without adding any new functions beyond the one proposed here.

Some more examples

Sum all columns in a table:

=SUM(SEGMENT(Ref#,TRUE))

Sum all rows in a table:

=SUM(SEGMENT(Ref#))

Pretty much all banks allow downloads to .csv files, so at the end of the month I often have a few such files open at the same time. To see content I select all and double-click to widen all columns. Then when I close the files I have to answer "don't save" to each one in turn.

This is a waste of time as I haven't changed any data, and column-widths can't be saved in a .csv file.

Can you please change excel so that it doesn't ask that question if a .csv file is being closed without having changed content?

Thanks!

Being a well-known name in the Marble Polishing industry, PMarble facilities never ever compromise in terms of quality, on-time deliverables and best Marble floor shine as guaranteed. Our marble floor polishing service is handled by a team of experts who employ cutting-edge strategies to provide service that meets the needs of our customers. For Free consultation, call us or WhatsApp Us - Phone No: +91 9716120389, 9877099747 or Marble Polishing Service in India

Read More: https://marblepolishingservice.in/marble-polishing-service/
https://marblepolishingservice.in/marble-diamond-polishing-service/
https://marblepolishingservice.in/marble-mirror-polishing-service/
https://marblepolishingservice.in/marble-wall-polishing-service/
https://marblepolishingservice.in/marble-floor-polishing-service/

If you are interested then check it on our official website:

A 52 Bharat vihar Street No 15, Raja Puri, Uttam Nager, New Delhi 110059

Phone No : +91 9716120389, 9877099747

marblepolishingservice.in

24 hrs

For the quicker recover of Excel Password, use the quickest Excel unluckier tool from perfect data solution that has excellent highlights to remove the solid password protection from the Excel Workbook other than with Excel Spreadsheet. three different cracking methods: Brute Force Attack, Dictionary Attack, and Mask Attack. This software is easy-to-use
Recover Excel Worksheet Password from lost or forgotten MS Excel password
Recover Password from MS Excel file and Excel Sheet Password
Excel Unlock tool recovers all types of passwords
instantly recover xls password and open protected Excel file
Recover Excel 2016 and 2013, 2010 Password, 2007, 2003, 2002(XP), 2000 and Excel 97
Excel Password Recovery Instantly recover excel workbook password or resets passwords to modify: workbook and worksheet protection, write reservation, etc.

Get. Red more-http://www.perfectdatasolutions.com/excel-password-recovery-software.html

The SysInspire Lotus Notes to Outlook converter is such a guaranteed application that perfectly converts the Lotus Notes to Outlook format keeping all its database in its original form. And yes, there will be no leaking out of the confidential data. Users safety is the 1st priority. The user will find no fluctuation in the file hierarchy even after the conversion of the Lotus notes nsf to outlook pst. The SysInspire tool ensures the user can convert Lotus Notes to Outlook without installation of MS Outlook on the computer.
To convert Lotus Notes to Outlook, take help of the Lotus Notes to Outlook converter by SysInspire.
Follow the below-given steps to import Lotus Notes to Outlook:-
Firstly, install the SysInspire Lotus Notes to Outlook.
Click on add nsf file option.
Choose the nsf file and click on Export in Outlook PST.
Select the required Lotus notes folder from the folder list and opt for scanning of files.
Then, consequently, put the filter options like PST split if required.
Browse for the target folder as per the need.
Click the Finish button.

Read more about:- https://www.sysinspire.com/nsf-to-pst-converter/