Nov 26 2021 06:36 AM
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 .
Thanks In advance
Priyank
Nov 26 2021 07:19 AM
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.
Nov 26 2021 07:23 AM
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
Nov 26 2021 07:48 AM
If to wait lambda helper functions which are so far available for insiders only
=MIN( BYROW( $E4:I8, LAMBDA(r, SUM(r) ) ) )
Dec 01 2021 03:05 AM
Thanks Hans, its working as required.
Dec 01 2021 03:10 AM