Help Needed: Excel Array

[Cardano]

I have an ARRAY in Excel e.g A1:F16.
For each row in this array e.g A1:F1, A2:F2 etc I want to check that the smallest value in each row is less than the smallest value in all of the Array but excluding the values in that row as it calculates each row in turn .
So for example when it comes to processing row A10:F10 I want to check that the smallest value in the row A10:F10 (excluding zeros ) is less than the smallest value in all of the Array but excluding that row A10:F10 from the calc.
Could someone please advise what function combination I need to do this.
Thanks in anticipation

[spreadbetting]

When you say "check that" do you mean "check if"?

Wouldn't it be easier to simply find the row containing the lowest number as that would be the only row where the smallest value in that row would be the smallest in the array. Unless I've read it wrong.

[sa7med]

I have an ARRAY in Excel e.g A1:F16.
For each row in this array e.g A1:F1, A2:F2 etc I want to check that the smallest value in each row is less than the smallest value in all of the Array but excluding the values in that row as it calculates each row in turn .
So for example when it comes to processing row A10:F10 I want to check that the smallest value in the row A10:F10 (excluding zeros ) is less than the smallest value in all of the Array but excluding that row A10:F10 from the calc.
Could someone please advise what function combination I need to do this.
Thanks in anticipation

Just take the min(min(A1:F9),min(A11:F100000)) no?

to exclude 0 you can replace this function instead of min
https://stackoverflow.com/questions/165 ... l/36615118

or you can have one column =MIN(IF(A1:F9>0;A1:F9)) (ctrl + shift + enter)
and another column = =MIN(IF(A11:F1000000>0;A11:F100000)) (ctrl + shift + enter)
and a third column to take the min of above to columns

[sa7med]

When you say "check that" do you mean "check if"?

Wouldn't it be easier to simply find the row containing the lowest number as that would be the only row where the smallest value in that row would be the smallest in the array. Unless I've read it wrong.

but there could be several hundred of equivalent mins

[Cardano]

Further explanation required.
So lets take a simple 6 x 3 array as follows

A1 B1 C1 D1 E1 F1
A2 B2 C2 D2 E2 F2
A3 B3 C3 D3 E3 F3

The array is populated with the following values for example

0 0 20.4 17.6 11.2 0
0 0 33.0 0 0 0
45.0 44.0 46.0 0 0 0

So when it comes to process row A1:F1 it calculates that the smallest value is = F1 i.e 11.2.
At the same time I want to check if F1 is the smallest value in the rest of the array i.e rows 2 and 3
As you can see then F1 (11.2) is less than the smallest value which is B2 (33.0)in the rest of the array inrows 2 and 3 and therefore would pass the test.
However, the smallest value in row A2:F2 is B2(33.0) but this would fail the check as B2(33.0) is not the smallest value in the rest of the array i.e rows1 and 3.
And likewise in Row 3 A3:F3 the smallest valu B3(44.0) is not the smallest value in the rest of the array i.e rows 1 and 2.
Hope that explains it a bit better

[CallumPerry]

Maybe I'm completely misunderstanding that explanation but it appears you only care about the absolute smallest value in the entire three-dimensional array, why bother doing this a row at a time? Could you not just locate the smallest value i.e. 11.2 and have the system locate which row this is on?

I assume you need the whole row for the next step of calcs but by finding the smallest total value - excluding 0's - and having the row number as the output would surely be the most efficient way of doing this?

[spreadbetting]

Maybe I'm completely misunderstanding that explanation but it appears you only care about the absolute smallest value in the entire three-dimensional array, why bother doing this a row at a time? Could you not just locate the smallest value i.e. 11.2 and have the system locate which row this is on?

I assume you need the whole row for the next step of calcs but by finding the smallest total value - excluding 0's - and having the row number as the output would surely be the most efficient way of doing this?

That's the way I saw it. Easy enough to find the min value in a row then rank those by min value, if you have joint rank 1's you have no row with a distinct smallest value.

[Cardano]

Thats right I am interested in the smallest value in each row (not the smallest value in the total array )
Each row will have a smallest value. So I am checking each row for it's smallest value and then checking if that smallest value in that particular row is less than the smallest value in the other arrays rows but excluding the row I am currently processing.
Hope that helps to clarify

[spreadbetting]

But the smallest value in one row will always be the lowest number on the whole array so it'd make sense to find that row rather than checking each row.

Probably help if you said how you want the output, or if you want VBA or formulas

[Cardano]

Ah, Ok
So having found the smallest value in a row I now need to check it against the second smallest value in the rest of the array,excluding the row currently being processed from the rest of the array check.
We are getting there I think

[spreadbetting]

Ah, Ok
So having found the smallest value in a row I now need to check it against the second smallest value in the rest of the array,excluding the row currently being processed from the rest of the array check.
We are getting there I think

There's no need whatsoever for the second smallest value in the array. What we're saying is if you're looking for a minimum value in the rows that is less than the smallest number in all the other rows it can only occur in the row that contains the smallest value. Even then it may not be smallest overall value if other rows also contain the same lowest number.

[Cardano]

Ok let me put it another way.
I am checking for the second smallest value in an Array and comparing that to the smallest value for each row and I am doing this for each row in turn.
So for the first row I find the second smallest value in the rest of the array (i.e ignoring completely the values in the first row ). Then when I've found the second smallest value (e.g it could be in the 10th row ) I compare it th the smallest value in the first row, and I want do the same process fo each row in turn.
Is there a function combination that would allow me to do that ?
Hope that helps

[sa7med]

Ok let me put it another way.
I am checking for the second smallest value in an Array and comparing that to the smallest value for each row and I am doing this for each row in turn.
So for the first row I find the second smallest value in the rest of the array (i.e ignoring completely the values in the first row ). Then when I've found the second smallest value (e.g it could be in the 10th row ) I compare it th the smallest value in the first row, and I want do the same process fo each row in turn.
Is there a function combination that would allow me to do that ?
Hope that helps

find the second smallest value using =SMALL(ARRAY,2) and compare against that

[Cardano]

I'm obvoiusly struggling to explain what I'm trying to do

For each row in turn I want to subtract that row from the array and then get the smallest or second smallest ( it doesn;t really matter ) from the remaining rows of the array e.g as follows
SMALL(ARRAY-Row(1),2)
SMALL(ARRAY-Row(2),2)
SMALL(ARRAY-Row(3),2)
etc

Can you subtract rows from arrays ?

[sa7med]

I'm obvoiusly struggling to explain what I'm trying to do

For each row in turn I want to subtract that row from the array and then get the smallest or second smallest ( it doesn;t really matter ) from the remaining rows of the array e.g as follows
SMALL(ARRAY-Row(1),2)
SMALL(ARRAY-Row(2),2)
SMALL(ARRAY-Row(3),2)
etc

Can you subtract rows from arrays ?

Doesnt my original reply do this? Find the SMALL up to that row, and the SMALL past that row