• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the minimum every line, and summarize 2

B

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

L

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.
 
B

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
 
L

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! :)
 
N

narunfca

Member
Lori said:
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! :)
Click to expand...
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.

Please check and confirm.

with best regards

Arun N
 
Last edited:
shrivallabha

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.
 
N

narunfca

Member
bines53 said:
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
Click to expand...
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
 
L

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)
 
B

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
 
L

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:
U

Ufoo

Member
Lori said:
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.
Click to expand...
Thanks Lori. Kindly please break this formula for the likes of me who are still new to excel. Thanks in advance
 
shrivallabha

shrivallabha

Excel Ninja
Ufoo said:
Thanks Lori. Kindly please break this formula for the likes of me who are still new to excel. Thanks in advance
Click to expand...
Ufoo,
This formula here has some parts associated to the previous challenge:
http://chandoo.org/forum/threads/find-the-minimum-every-line-and-summarize.31427/#post-187524
for which Narayan has explained so painstakingly:
http://chandoo.org/forum/threads/find-the-minimum-every-line-and-summarize.31427/#post-187536

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!
 
U

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
shrivallabha said:
Ufoo,
This formula here has some parts associated to the previous challenge:
http://chandoo.org/forum/threads/find-the-minimum-every-line-and-summarize.31427/#post-187524
for which Narayan has explained so painstakingly:
http://chandoo.org/forum/threads/find-the-minimum-every-line-and-summarize.31427/#post-187536

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!
Click to expand...
 
You must log in or register to reply here.
Top