Excel Formula Query

Cotta

I've just taken over an old spreadsheet that somebody else setup, the following code is on it - can anyone explain what it means please?


=(L11-M11)+ROW()/1000000


Thanks

spadoosh

cell minus cell, add the sum of the row in which the formula is in, divided by one million.

ROW() means add the total value of what is in the row in which the forumla is in. If there is a number in the brackets it relates to a specific row.

rmg1

The ROW() bit of the formula give the row number the formula is in. I guess it's being divided by 1000000 to give a tiny number to try and stop duplicate values in the list (useful if you're trying to rank numbers or similar).

Cotta

Thanks all, one final point.

L11 = 10
M11 = 5

Formula returns 5.000023, should it not be 5.000005 as 5+5/1000000 = 5.000005

wealdroam

Cotta wrote: »

Thanks all, one final point.

L11 = 10
M11 = 5

Formula returns 5.000023, should it not be 5.000005 as 5+5/1000000 = 5.000005

If there is nothing else in row 11, I think it should return 5.000015
i.e. (10 - 5) + (10 + 5)/1000000

Le_Kirk

Which cell contains your formula? Is it in row 23 or row 5?
L11 - M11 = 5
Row() = 11 (if your formula is in row 11 like mine was)
11/1000000 = 0.000011
Total = 5.000011


The reference ROW() adds the number of the row NOT the value of anything in that row.

Cotta

Le_Kirk wrote: »

Which cell contains your formula? Is it in row 23 or row 5?
L11 - M11 = 5
Row() = 11 (if your formula is in row 11 like mine was)
11/1000000 = 0.000011
Total = 5.000011


The reference ROW() adds the number of the row NOT the value of anything in that row.

Perfect, I didn't realise it was using the row number.

Thanks everyone for your help.

spadoosh

Sorry that wouldve been my mistake! Thought it was the sum of the row... oops.