Dynamic Arrays: applying formulas row per row

[ninjatunez]

ExcelQ.jpg

In image I am creating dynamic arrays in 5 columns going downwards. The logic for black text is per row it works out (manually) what is the minimum of value in column A and B then it publishes if the minimum is in "A" or "not A".

I would like to have a single formula like red text that uses MIN formula instead of requiring me to manually work it out using IF. However when I pass A3#:B3# to MIN() it returns true only if value is minimum over the entire range.

How do I do a formula so that I can pass to MIN only the values row per row so it evaluates the same as columns C & D formulas?

For example this is needed if I had 4 or more columns of random numbers and I wanted to check if the A column was the lowest of them all. With IF it would need very nasty nested statements.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. You have it all set out in front of you already, so let's have a sheet, not a picture of one.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[ninjatunez]

Apologies I have added an Excel attachment which I hope explains it fully

[ninjatunez]

I assume there is no solution - Excel dynamic arrays simply aren't built for use like this!

[Glenn Kennedy]

I didn't really understand your requirement, so I moved on, hoping someone else would pick it up!!

[romperstomper]

I'd stick with the IF function, otherwise I think you need something like:

=IF(SUBTOTAL(5,OFFSET(A6#:B6#,SEQUENCE(COUNTA(A6#))-1,0,1,2))=A6#,"A","not A")

to get it to evaluate each row individually.

[ninjatunez]

This is amazing and perfect many many thanks!
I'll be using this with LAMBDA to make my own set of functions that can apply to dynamic spilled arrays e.g. LMIN, LAVERAGE, LSUM etc.

[ninjatunez]

This is great for MIN, SUM, AVERAGE etc. that SUBTOTAL caters for. However I would like to use logic operators like AND, OR. Would you know how to do these?
So if A6# and B6# were 2 dynamic columns of TRUE,FALSE how can it evaluate each row individually for AND, OR?

Many many thanks if you can figure this one out!

[romperstomper]

Can you give me a specific example of what you want? That sounds like something you'd use FILTER for instead.

[tboulden]

Perhaps I'm late, but does this do what you want? Change ":" to "," in your MIN. =IF(MIN(A6#,B6#)=A6#,"A","not A")

[romperstomper]

That has the same problem as the original version - i.e. it only takes the min of all the rows, not on a per row basis.

[tboulden]

Ahh, reading comprehension fail on my part, thanks!