New Contributor

Dynamically Need to find out minimum value of each row after summation of multiple columns.

Hi

I may not be very clear in describing subject. However I will try my best to elaborate issue here.

I have to calculate minimum for each week and as we move to next week minimum should be calculated after summation till current week for each week. As shown in below screenshot

I am using minimum function =MIN(SUM(\$E\$4:I4),SUM(\$E\$5:I5),SUM(\$E\$6:I6),SUM(\$E\$7:I7),SUM(\$E\$8:I8)) and getting correct result. However this formula is not dynamic. If any additional rows added formula need to be adjusted manually.

I am looking for more dynamic way to handle this .

Priyank

5 Replies

Re: Dynamically Need to find out minimum value of each row after summation of multiple columns.

Here is a custom VBA function that you can use

``````Function MinRowSum(rng As Range) As Double
Dim r As Range
Dim m As Double
MinRowSum = 1E+308
For Each r In rng.Rows
MinRowSum = Application.Min(Application.Sum(r), MinRowSum)
Next r
End Function``````

Leave one empty row between the data and the row with minimum values. So in your example, enter the formulas in row 10,

In E10:

=MinRowSum(\$E\$4:E8)

Fill to the right to I10.

If you add a new week, fill the formula one more cell to the right.

If you want to add a new row, copy the last row with data and insert it into the same row, then edit its values.

The formulas will then include the new row.

Re: Dynamically Need to find out minimum value of each row after summation of multiple columns.

I cannot see a good approach using min() in this case, because you are not getting the minimun for that week but the minimun for the all dataset.

Your formulas gives the result the following

=MIN(2,4,4,2,6)

This is the same of the column D. So would be better you just apply the MIN() formula there.

But in any case If you need to apply the formula the way you developed, I suggest you insert a table on existing data so everytime you need to include another row, the formula will be updated automatically

Re: Dynamically Need to find out minimum value of each row after summation of multiple columns.

If to wait lambda helper functions which are so far available for insiders only

``=MIN( BYROW( \$E4:I8, LAMBDA(r, SUM(r) ) ) )``

Re: Dynamically Need to find out minimum value of each row after summation of multiple columns.

Thanks Hans, its working as required.

Re: Dynamically Need to find out minimum value of each row after summation of multiple columns.

Hi Sergei, Thanks for your input. Definitely I am looking forward to try this function. However , Hans suggestion has worked fine for me.