Good evening,
as the title suggests, I need to find in a sum,
the minimum value other than 0.
In the attached file in column M I report the minimum values of the sums of various columns.
in cell M2 it gives me the minimum value which is 0
but, excluding 0, it should be 45.58
Thanks
Please try
=AGGREGATE(15;6;1/(1/SUMIFS(F2:K2,F$1:K$1,{"A";"B";"C"}));1)
or
=AGGREGATE(15;6;1/(1/CHOOSE({1;2;3};F2+G2;H2+I2;J2+K2));1)
ummmm...your current formula is looking at the entire horizontal range in row 2. the val you want is in cell h6 and sums 2 vals that are unrelated to your current function. try this is cell m2 and see if it gives you a boost and/or insight into what you want:
taken from:
=SMALL(F2:K2,COUNTIF(F2:K2, 0)+1)
https://support.microsoft.com/en-us/...1-f75bb9bb89bd
https://www.google.com/search?q=exce...d+lowest+value
ok this is okay
=AGGREGATE(15;6;1/(1/CHOOSE({1;2;3};F2+G2;H2+I2;J2+K2));1)
one last thing
wanting to color the two cells that correspond to the minimum
how to set the formula in conditional formatting?
Thanks
Please try CF formula applies to F6:K8
=F6=$M2
or CF formula applies to F2:K4
=SUMIFS($F2:$K2,$F$1:$K$1,F$1)=$M2
Last edited by Bo_Ry; 12-17-2020 at 04:13 PM.
Sorry, I'm the one who didn't explain myself well.
I would like it on F2: K4
the sum of 2 cells = M2
I did 3
...
= $ H2 + $ I2 = $ M2
It applies to:
= $ H $ 2: $ I $ 4
...
you can give it unique
Someone has to suggest brute force with array formulas.
M2:
Formula:
=MIN(IF(MMULT(F2:K2,{1,0,0;1,0,0;0,1,0;0,1,0;0,0,1;0,0,1}),MMULT(F2:K2,{1,0,0;1,0,0;0,1,0;0,1,0;0,0,1;0,0,1})))
Fill M2 down as far as needed. Enter array formulas by holding down [Ctrl] and [Shift] keys before pressing [Enter].
The array in the formula above could be generated by
Formula:=--(TRANSPOSE(INT((COLUMN(F2:K2)-MIN(COLUMN(F2:K2)))/2))={0,1,2})
so that if you wanted sums of 3 columns it'd change to
Formula:=--(TRANSPOSE(INT((COLUMN(F2:K2)-MIN(COLUMN(F2:K2)))/3))={0,1})
or if you wanted the sums of 5 columns from F2:Y2, it'd change to
Formula:=--(TRANSPOSE(INT((COLUMN(F2:Y2)-MIN(COLUMN(F2:Y2)))/5))={0,1,2,3})
The number of adjacent columns to sum (n) become the divisor inside the INT call, and the ending array are sequential integers from 0 to the total number of columns divided by n less 1.
Last edited by hrlngrv; 12-17-2020 at 04:49 PM. Reason: correction
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks