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

%3CLINGO-SUB%20id%3D%22lingo-sub-3008839%22%20slang%3D%22en-US%22%3EDynamically%20Need%20to%20find%20out%20minimum%20value%20of%20each%20row%20after%20summation%20of%20multiple%20columns.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3008839%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20may%20not%20be%20very%20clear%20in%20describing%20subject.%20However%20I%20will%20try%20my%20best%20to%20elaborate%20issue%20here.%3C%2FP%3E%3CP%3EI%20have%20to%20calculate%20minimum%20for%20each%20week%20and%20as%20we%20move%20to%20next%20week%20minimum%20should%20be%20calculated%20after%20summation%20till%20current%20week%20for%20each%20week.%20As%20shown%20in%20below%20screenshot%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22priyanksharma_0-1637937127659.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329705iDB7B58957EA3D390%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22priyanksharma_0-1637937127659.png%22%20alt%3D%22priyanksharma_0-1637937127659.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20using%20minimum%20function%26nbsp%3B%3DMIN(SUM(%24E%244%3AI4)%2CSUM(%24E%245%3AI5)%2CSUM(%24E%246%3AI6)%2CSUM(%24E%247%3AI7)%2CSUM(%24E%248%3AI8))%20and%20getting%20correct%20result.%20However%20this%20formula%20is%20not%20dynamic.%20If%20any%20additional%20rows%20added%20formula%20need%20to%20be%20adjusted%20manually.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20more%20dynamic%20way%20to%20handle%20this%20.%3C%2FP%3E%3CP%3EThanks%20In%20advance%3C%2FP%3E%3CP%3EPriyank%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3008839%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3009084%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20Need%20to%20find%20out%20minimum%20value%20of%20each%20row%20after%20summation%20of%20multiple%20columns.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3009084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1230503%22%20target%3D%22_blank%22%3E%40priyanksharma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%20that%20you%20can%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20MinRowSum(rng%20As%20Range)%20As%20Double%0A%20%20%20%20Dim%20r%20As%20Range%0A%20%20%20%20Dim%20m%20As%20Double%0A%20%20%20%20MinRowSum%20%3D%201E%2B308%0A%20%20%20%20For%20Each%20r%20In%20rng.Rows%0A%20%20%20%20%20%20%20%20MinRowSum%20%3D%20Application.Min(Application.Sum(r)%2C%20MinRowSum)%0A%20%20%20%20Next%20r%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ELeave%20one%20empty%20row%20between%20the%20data%20and%20the%20row%20with%20minimum%20values.%20So%20in%20your%20example%2C%20enter%20the%20formulas%20in%20row%2010%2C%3C%2FP%3E%0A%3CP%3EIn%20E10%3A%3C%2FP%3E%0A%3CP%3E%3DMinRowSum(%24E%244%3AE8)%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20to%20I10.%3C%2FP%3E%0A%3CP%3EIf%20you%20add%20a%20new%20week%2C%20fill%20the%20formula%20one%20more%20cell%20to%20the%20right.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20add%20a%20new%20row%2C%20copy%20the%20last%20row%20with%20data%20and%20insert%20it%20into%20the%20same%20row%2C%20then%20edit%20its%20values.%3C%2FP%3E%0A%3CP%3EThe%20formulas%20will%20then%20include%20the%20new%20row.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3009245%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20Need%20to%20find%20out%20minimum%20value%20of%20each%20row%20after%20summation%20of%20multiple%20columns.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3009245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1230503%22%20target%3D%22_blank%22%3E%40priyanksharma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20to%20wait%20lambda%20helper%20functions%20which%20are%20so%20far%20available%20for%20insiders%20only%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DMIN(%20BYROW(%20%24E4%3AI8%2C%20LAMBDA(r%2C%20SUM(r)%20)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

priyanksharma_0-1637937127659.png

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

 

5 Replies

@priyanksharma 

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.

 

@priyanksharma 

 

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

@priyanksharma 

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

=MIN( BYROW( $E4:I8, LAMBDA(r, SUM(r) ) ) )
Hi Sergei, Thanks for your input. Definitely I am looking forward to try this function. However , Hans suggestion has worked fine for me.