Custom functions
 

I have a formula that I use to balance my check book. If column D of the
current row (row of the current selected cell) is not 'P', It takes the value
in column G of the previous row, subtracts the value in column E of the
current row, adds the value in cell F in the current row and populates it
into column G of the current row. If column D of the current row (row of the
current selected cell) IS 'P', then column G of the current row is equal to
column G of the previous row.

Currently, I copy and paste the formula from cell (G,n-1) to cell (G,n). How
can I make this into a function?

Custom functions
 

Why not just make it an IF() statement that you can 'fill' down the sheet in
column G as needed?

Assuming some column titles in row 1, and row 2 has starting information
(starting balance in G2), then in G3 enter this formua:
=IF(D3="P",G2,G2-E3+F3)

Then fill that formula down the sheet as far as you need it to go, and
continue filling further as your list of entries in the other columns grows
longer.

See Excel Help on 'Fill Data' or 'Fill data in worksheet cells' and the
sub-topic(s) 'use the fill handle to fill data' and 'Fill formulas into
adjacent cells' for specifics on how to extend (fill) the formula on down the
sheet without having to do individual copy/paste operations.

"GKW in GA" wrote:

I have a formula that I use to balance my check book. If column D of the
current row (row of the current selected cell) is not 'P', It takes the value
in column G of the previous row, subtracts the value in column E of the
current row, adds the value in cell F in the current row and populates it
into column G of the current row. If column D of the current row (row of the
current selected cell) IS 'P', then column G of the current row is equal to
column G of the previous row.

Currently, I copy and paste the formula from cell (G,n-1) to cell (G,n). How
can I make this into a function?

Custom functions
 

"fill' down the sheet in column G as needed?" is what I am currently doing.
I guess I just wanted to learn how to create a function out of it.

"JLatham" wrote:

Why not just make it an IF() statement that you can 'fill' down the sheet in
column G as needed?

Assuming some column titles in row 1, and row 2 has starting information
(starting balance in G2), then in G3 enter this formua:
=IF(D3="P",G2,G2-E3+F3)

Then fill that formula down the sheet as far as you need it to go, and
continue filling further as your list of entries in the other columns grows
longer.

See Excel Help on 'Fill Data' or 'Fill data in worksheet cells' and the
sub-topic(s) 'use the fill handle to fill data' and 'Fill formulas into
adjacent cells' for specifics on how to extend (fill) the formula on down the
sheet without having to do individual copy/paste operations.

"GKW in GA" wrote:

I have a formula that I use to balance my check book. If column D of the
current row (row of the current selected cell) is not 'P', It takes the value
in column G of the previous row, subtracts the value in column E of the
current row, adds the value in cell F in the current row and populates it
into column G of the current row. If column D of the current row (row of the
current selected cell) IS 'P', then column G of the current row is equal to
column G of the previous row.

Currently, I copy and paste the formula from cell (G,n-1) to cell (G,n). How
can I make this into a function?

Custom functions
 

Although it can be done, I wouldn't advise to do this via a function.
You can trick the system, but that is not wise and you can't be sure of compatibility for future versions.
That means you'll have to include all precedent cells in the argument list of the function; it would be called like this:

=MyFunction(D3,G2,E3,F3)

Hardly any advantage over your formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"GKW in GA" wrote in message ...
|
| "fill' down the sheet in column G as needed?" is what I am currently doing.
| I guess I just wanted to learn how to create a function out of it.
|
| "JLatham" wrote:
|
| Why not just make it an IF() statement that you can 'fill' down the sheet in
| column G as needed?
|
| Assuming some column titles in row 1, and row 2 has starting information
| (starting balance in G2), then in G3 enter this formua:
| =IF(D3="P",G2,G2-E3+F3)
|
| Then fill that formula down the sheet as far as you need it to go, and
| continue filling further as your list of entries in the other columns grows
| longer.
|
| See Excel Help on 'Fill Data' or 'Fill data in worksheet cells' and the
| sub-topic(s) 'use the fill handle to fill data' and 'Fill formulas into
| adjacent cells' for specifics on how to extend (fill) the formula on down the
| sheet without having to do individual copy/paste operations.
|
| "GKW in GA" wrote:
|
| I have a formula that I use to balance my check book. If column D of the
| current row (row of the current selected cell) is not 'P', It takes the value
| in column G of the previous row, subtracts the value in column E of the
| current row, adds the value in cell F in the current row and populates it
| into column G of the current row. If column D of the current row (row of the
| current selected cell) IS 'P', then column G of the current row is equal to
| column G of the previous row.
|
| Currently, I copy and paste the formula from cell (G,n-1) to cell (G,n). How
| can I make this into a function?