Can I do function like
=Minus(H(row), G(row))
and the Row come become number where the function locate?
Can I do function like
=Minus(H(row), G(row))
and the Row come become number where the function locate?
Hi Thugy and welcome to the Forum.
You can use the INDIRECT and ROW functions to do that. To subtract G5 from H5 say, put this formula in any cell of row 5:
=INDIRECT("H" & ROW())-INDIRECT("G"&ROW())
Alternatively you can use relative addressing with R1C1 reference addresses (but you would need to select that in Excel settings and the +/- column differences depend on the cell containing the formula). To do that, go menu File/Options/Formulas then click/check R1C1 reference style (under "Working with Formulas"). Then enter this in cell I5 say:
=RC[-1] - RC[-2]
which means take the cell no rows and -1 columns to the right of I5 (i.e. 1 to the left) and subtract what's in that two to the left (those numbers in bold above). That's much shorter but the trouble is(or maybe) that R1C1 addressing will be applied to all simple formulae (like =H5-G5) and can be confusing.
You can still use that addressing while in (normal) A1 mode, by using:
=INDIRECT("RC[-1]",FALSE)-INDIRECT("RC[-2]",FALSE)
(where the FALSE argument means INDIRECT uses R1C1 addresses instead) but I think the first formula will make most sense to most users..
Hope this helps. If so, please remember to mark this Answer as Selected.