Hi, All,
I just need to create a sheet for a piggy bank idea.
So say, Column A I place in a number and Column B displays that number and keeps it as the Total.
I know how to set this up…
But what I need is for Column A to go blank once its number is added in Column B.
Overall, Column B must somehow remember past entered numbers as it tallies up the Total.
Is there a way to do this?
Thanks for your help! :)
iMac 21.5", macOS 10.14
Is there a way to do this?
Not the way you've described it.
Spreadsheets do not 'remember' past actions. Formulas calculate on the current content of cells they reference.
Assuming that ALL transactions with the piggy will be deposits, you may be able to achieve what you want using tiny serial values in a single column as the input cell, A seond column in the same table to provide a filtering key that will restrict the first column to displaying a single cell, into which the next coin drop would be recorded, and a second, single cell table containing the sum of column A, truncated to two decimal places as the Total display.
Here's a sample,showing the 'man behind the curtain pulling the levers:
The top few rows of Table 1 (column A) are shown.
Note that row 1, a Header row, is missing from the image.
Each cell below that originally contains the formula below, entered in A2, then filled down for as many rows as are expected to be needed. The formula creates a very small number that is sightly bigger in each succeeding row.
A2: ROW()÷100000000000000
Cell A1 contains a formula that returns the minimum value in (the rest of) column A.
A1: MIN(A)
Note that Row 1 must be defined as a Header row to make the cell reference "A" refer to all of the non-header rows in column A.
All cells in Column A (except A1, in the Header row) have a conditional hilighting rule applied that changes the text colour and cell fill colour to white if the contents of that cell match the contents of cell A$1 The result can be seen in A4 above.
Column B contains a third formula, which marks the row containing the smallest value in column A, providing a filter key that permits displaying only that row. The formula is entered in B2, then filled down to the last row of column B.
B2: A$1=A2
The formula returns true if the value in A1 matches the value in 'this row' of column A, and returns false if the two values do not match.
In the Filter inspector pane, the rule shown below tells the table to display only the row containing true in column B.
With column B hidden, and the filter applied, the table above has this appearance:
Entering 25 in the 'empty' box replaces the formula with the value entered, and hides the row 4 cell as it no longer contains the minimum value in the column.
25 typed, but not yet entered
Enter key (return) pressed.
In the Total table, cell A1 contains the following formula, which sums column A of Table 1, and divides the result by 100 to present the current total, entered as cents, as dollars and cents.
Total::A1: SUM(Table 1::A)÷100
Thanks for the interesting exercise.
Regards,
Barry
Is there a way to do this?
Not the way you've described it.
Spreadsheets do not 'remember' past actions. Formulas calculate on the current content of cells they reference.
Assuming that ALL transactions with the piggy will be deposits, you may be able to achieve what you want using tiny serial values in a single column as the input cell, A seond column in the same table to provide a filtering key that will restrict the first column to displaying a single cell, into which the next coin drop would be recorded, and a second, single cell table containing the sum of column A, truncated to two decimal places as the Total display.
Here's a sample,showing the 'man behind the curtain pulling the levers:
The top few rows of Table 1 (column A) are shown.
Note that row 1, a Header row, is missing from the image.
Each cell below that originally contains the formula below, entered in A2, then filled down for as many rows as are expected to be needed. The formula creates a very small number that is sightly bigger in each succeeding row.
A2: ROW()÷100000000000000
Cell A1 contains a formula that returns the minimum value in (the rest of) column A.
A1: MIN(A)
Note that Row 1 must be defined as a Header row to make the cell reference "A" refer to all of the non-header rows in column A.
All cells in Column A (except A1, in the Header row) have a conditional hilighting rule applied that changes the text colour and cell fill colour to white if the contents of that cell match the contents of cell A$1 The result can be seen in A4 above.
Column B contains a third formula, which marks the row containing the smallest value in column A, providing a filter key that permits displaying only that row. The formula is entered in B2, then filled down to the last row of column B.
B2: A$1=A2
The formula returns true if the value in A1 matches the value in 'this row' of column A, and returns false if the two values do not match.
In the Filter inspector pane, the rule shown below tells the table to display only the row containing true in column B.
With column B hidden, and the filter applied, the table above has this appearance:
Entering 25 in the 'empty' box replaces the formula with the value entered, and hides the row 4 cell as it no longer contains the minimum value in the column.
25 typed, but not yet entered
Enter key (return) pressed.
In the Total table, cell A1 contains the following formula, which sums column A of Table 1, and divides the result by 100 to present the current total, entered as cents, as dollars and cents.
Total::A1: SUM(Table 1::A)÷100
Thanks for the interesting exercise.
Regards,
Barry
Simple Piggy Bank Sheet for Numbers
