Row dependent function

Copper Contributor

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.

12 Replies

@jomacapdb1 

How about

 

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+INDIRECT(ADDRESS(ROW(),COLUMN()-2))

@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.

@jomacapdb1 

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.

@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?

@jomacapdb1 

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.

Spoiler
 

@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.

@jomacapdb1 

Sorry about that. Change the formula to

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))

@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.

@jomacapdb1 

Hold down the Ctrl key when you click on the ✓ icon or press Enter.

@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.

@jomacapdb1 

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:

  • Select G7:G100
  • Enter the formula =G6-E7
  • Press Ctrl+Enter

@Hans Vogelaar Thank you again for the info. Useful to know for future reference.