Oct 10 2020 07:53 AM
Want to form a function that is row dependent.
For given columns E & G, the following functions appear in cells G7, G8 & G9 :
G7=G6-E7
G8=G7-E8
G9=G8-E9
Form a general function of these equations so that there is no need to individually type in each equation for each cell.
Oct 10 2020 08:11 AM
Oct 10 2020 09:59 AM
@Hans Vogelaar What goes inside the brackets? Does this formula need to be inserted into the relevant cell for each row? Not yet tried it out.
Oct 10 2020 11:49 AM
Nothing goes inside the brackets. ROW() returns the row number of the cell with the formula, and COLUMN() the column number of the cell with the formula.
Select the range into which you want the formulas, enter or copy/paste the formula from my previous reply and confirm it with Ctrl+Enter to enter it in all cells of the selection.
Oct 11 2020 02:15 AM
@Hans Vogelaar Still require some more assistance before trying it out.
1. How / where do you select the range for this formula? I suppose that could be the start cell and the end cell of the column for this formula.
2. You say copy / paste the formula. But into which cell, or whereabouts on the spreadsheet?
Oct 11 2020 02:23 AM
Let's say that you want to apply the formula to G7:G100.
Select this range G7:G100.
Click in the formula bar.
If there is already a formula, delete it.
Type the formula from my reply manually, or copy it from that reply and paste it into the formula bar.
Press Ctrl+Enter.
Oct 11 2020 08:17 AM
@Hans Vogelaar Progress of sorts. Instead of subtracting E values from G values, the resulting G values have the E values added to them. For example, G7=G6+E7, and not G7=G6-E7. Going down the G column results in increasing G values, whereas G needs to decrease in value. Over to you again.
Oct 11 2020 08:21 AM
Sorry about that. Change the formula to
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))
Oct 12 2020 03:20 AM
@Hans Vogelaar Minus instead of Plus, of course, I should have spotted that.
This is what happens at present. G values now decrease going down the G column.
But, I select a series of cells in the G column, say G7 to G20. I paste the formula into the formula bar. I click the tick symbol. The correct value appears in G7, but the remaining G8 to G20 cells are deselected, and do not show any decreasing values. If I select each cell individually, and click the formula in the formula bar, then each cell shows the correct value. But this is time consuming. I am looking to get the G values to appear simultaneously from a single click of the formula.
Oct 12 2020 03:31 AM
Hold down the Ctrl key when you click on the ✓ icon or press Enter.
Oct 12 2020 04:19 AM
@Hans Vogelaar Got it, instantaneous and simultaneous. Thank you for your assistance. As an aside, is this formula and procedure to be found somewhere else within Excel as a standard formula and procedure? Or did you produce it in order to answer my enquiry? If necessary, I would be interested to learn how to produce other non-standard formulae and procedures any time in the future.
Oct 12 2020 04:33 AM
The formula itself is specific for your request.
Ctrl+Enter to enter a formula in all cells of the selected range is a standard Excel shortcut. You can find it in (for example) Keyboard shortcuts in Excel
By the way, this would also have worked:
Oct 12 2020 05:48 AM
@Hans Vogelaar Thank you again for the info. Useful to know for future reference.