# Find the minimum every line, and summarize 2

#### bines53

##### Active Member
Hello friends,

This time, three columns.

To find the amount of 53, as it appears in the formula in cell D13, column E is illustrative.

Without the use of volatile functions.

David

#### Attachments

• 8.3 KB Views: 45
• Lori

#### bines53

##### Active Member
Hi @ shrivallabha,

Yes, I saw, I'm waiting for some other ideas.

David

#### Lori

##### Active Member
Hi David, Can you make additional assumptions on the type of numbers in the range?

For example assuming integers between 0 and 99 and data starting in row 1 you could try with CSE:

=SUM(MOD(LARGE(100*ROW(A1:C10)+A1:C10,ROW(A1:C10)*COLUMNS(A1:C10)),100))

The formula quoted by Shrivallabha was allowing for arbitrary numbers like =RAND(). This type of formula would lose precision with those type of values.

#### bines53

##### Active Member
Hi @ Lori,

Just that I was looking for,

=SUM(MOD(LARGE(MAX(A1:C10)*ROW(A1:C10)+A1:C10,ROW(A1:C10)*COLUMNS(A1:C10)),MAX(A1:C10))) ,with CSE.

I want to say, I relied on your formula, the previous challenge.
I checked if it is possible to shorten.

David

• shrivallabha

#### Lori

##### Active Member
Hi David,

That formula works for the particular data set and for data containing whole numbers up to about 8 digits long if data can be located anywhere on the sheet. So for things like dates, scores and record numbers it may make sense to use such a formula particularly on large record sets where you would get a significant speed improvement.

However, it would need adapting to allow for negative or decimal values eg currency values or scientific measurements, so it would be good to attach these further details with the challenge description! #### narunfca

##### Member
Hi David,

That formula works for the particular data set and for data containing whole numbers up to about 8 digits long if data can be located anywhere on the sheet. So for things like dates, scores and record numbers it may make sense to use such a formula particularly on large record sets where you would get a significant speed improvement.

However, it would need adapting to allow for negative or decimal values eg currency values or scientific measurements, so it would be good to attach these further details with the challenge description! The following formula seems working even for negative and decimal values.

=SUM(MOD(LARGE(((ROUNDUP(MAX(A1:D20),0)-MIN(A1:D20,0))*ROW(A1:D20)-MIN(A1:D20,0))+A1:D20,ROW(A1:D20)*COLUMNS(A1:D20)),(ROUNDUP(MAX(A1:D20),0)-MIN(A1:D20,0))))+MIN(A1:D20,0)*ROWS(A1:D20) CSE

assumed 20 rows and 4 columns.

with best regards

Arun N

Last edited:
• Lori

#### shrivallabha

##### Excel Ninja
That logic of Lori's so beautiful that one just needs couple of tweaks to reverse the condition for finding MAX:
=SUM(MOD(SMALL(MAX(A1:C10+1)*ROW(A1:C10)+A1:C10,ROW(A1:C10)*COLUMNS(A1:C10)),MAX(A1:C10+1)))
Just for posterity.

#### narunfca

##### Member
Hello friends,

This time, three columns.

To find the amount of 53, as it appears in the formula in cell D13, column E is illustrative.

Without the use of volatile functions.

David
A simple function for 3 columns.

=SUM(IF(IF(A1:A10<B1:B10,A1:A10,B1:B10)<C1:C10,IF(A1:A10<B1:B10,A1:A10,B1:B10),C1:C10))

A simple function for 4 columns.

=SUM(IF(IF(A1:A10<B1:B10,A1:A10,B1:B10)<IF(C1:C10<C1:C10,C1:C10,D1:D10),IF(A1:A10<B1:B10,A1:A10,B1:B10),IF(C1:C10<D1:D10,C1:C10,D1:D10)))

Interestingly the nesting requirements are binary. i.e, 8 columns requires only 3 levels of nesting.

with best regards
Arun N

• bines53

#### Lori

##### Active Member
@narunfca: The formula adjustment looks good except perhaps for an additional +1 adjustment as in Shriva's formula in case the maximum value coincides with the minimum of the row (eg changing A9 to 25 and B9 to 25 in the example)

One more non-CSE option for fun:

=SUMPRODUCT(HLOOKUP(1,FREQUENCY(ROW(A1:C10),ROW(A1:C10)+PERCENTRANK.EXC(A1:C10,A1:C10,6)),TREND(2*COUNT(A1:C10)+1^A1:C10,,,0)/3),A1:C10)

#### bines53

##### Active Member
Hello friends,

I want to apologize, of course this challenge, should also include negative numbers, I forgot to do tests with negative numbers in my formula.
If the data are positive values, I do not see any problem in my formula.

Lori,
When we have a very large data ,bit difficult for me with that kind, RANK(A1:C10,A1:C10) or PERCENTRANK.EXC(A1:C10,A1:C10,6),
It seems to me they are less efficient with large data.

Arun,
The formula in post #9, I liked the graduated structure of the formula.

regards
David

#### Lori

##### Active Member
Hi David,

Yes you're right about inefficiency of rank functions on large data sets but they do provide one sure way of maintaining full accuracy. Otherwise you may need to worry about loss of precision when adding and subtracting large numbers for example with data like
=PI() near the end of the sheet only about 8 or 9 digits of the number returned will be accurate.

For the formula above, i think a possible subtitute for PERCENTRANK(A1:C10,A1:C10,6) is T.DIST(A1:C10,1,2) which would improve efficiency while maintaining a high degree of precision and keeping formula length to a minimum.

Anyway, thanks for a thought provoking challenge! Lori

Last edited:
• Thomas Kuriakose

#### Ufoo

##### Member
Hi David, Can you make additional assumptions on the type of numbers in the range?

For example assuming integers between 0 and 99 and data starting in row 1 you could try with CSE:

=SUM(MOD(LARGE(100*ROW(A1:C10)+A1:C10,ROW(A1:C10)*COLUMNS(A1:C10)),100))

The formula quoted by Shrivallabha was allowing for arbitrary numbers like =RAND(). This type of formula would lose precision with those type of values.
Thanks Lori. Kindly please break this formula for the likes of me who are still new to excel. Thanks in advance

#### shrivallabha

##### Excel Ninja
Thanks Lori. Kindly please break this formula for the likes of me who are still new to excel. Thanks in advance
Ufoo,
This formula here has some parts associated to the previous challenge:
for which Narayan has explained so painstakingly:

If I were you, I'd break head for finding out how it works and if few more brain cells were left at the end of exercise then I'd spend time on how I can use it (formula or concepts) to real life scenario. So if you care and want to gain and apply knowledge you should not ask for tailor made explanation for each posting. Instead, start breaking your head on them There will be times when you will not understand something but then all you should have is specifics that you don't understand not the whole thing!

#### Ufoo

##### Member
You are right. I have been a bit lazy. But since I am still new to excel I want to understand concepts. Thanks
Ufoo,
This formula here has some parts associated to the previous challenge:
If I were you, I'd break head for finding out how it works and if few more brain cells were left at the end of exercise then I'd spend time on how I can use it (formula or concepts) to real life scenario. So if you care and want to gain and apply knowledge you should not ask for tailor made explanation for each posting. Instead, start breaking your head on them 