I need to perform calculations on a block of cells and return the minimum of all the calculations.
I need to divide D1 by A1, D2 by A2, D3 by A3…, then I need to get the minimum of those calculations and return either the entire row or the absolute row address where that minimum is found. Any suggestions?
Hi delijantzen, the next formula seems works, but not if there are zero values in A column:
=OFFSET(A$1;SUMPRODUCT(($D$1:$D$100/$A$1:$A$100)=SMALL($D$1:$D$100/$A$1:$A$100;1);ROW($A$1:$A$100))-ROW($A$1);0)
Change the first OFFSET() parameter to get the value from the column you want, and you can copy in the next columns.
{=OFFSET($A$1:$D2;SUMPRODUCT(($D$1:$D$100/$A$1:$A$100)=SMALL($D$1:$D$100/$A$1:$A$100;1);ROW($A$1:$A$100))-ROW($A$1);0)}
It is the same but for a range, you need to enter as array → Ctrl+Shift+Enter not only Enter (do not enter the brackets { })