Ask Your Question
0

Calculating a block of cells with Calc? [closed]

asked 2013-10-17 23:41:45 +0100

delijantzen gravatar image

updated 2020-08-07 03:25:46 +0100

Alex Kemp gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-12 02:16:27.784677

1 Answer

Sort by » oldest newest most voted
1

answered 2013-10-18 03:45:56 +0100

m.a.riosv gravatar image

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 { })

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-10-17 23:41:45 +0100

Seen: 107 times

Last updated: Oct 18 '13