Hi saeedoom,
"is there a formula that transferring the data and if a chosen cell used "<=0" then it moves the data to a new row. 🙂"
Each formula sets the value only in the cell that it occupies, and cannot use the value in that cell to determine the value in that cell. Attempting that will produce a self reference error.
In the example below, there are two tables. The Transaction table is used for recording each transaction and for calculation the share going to or due from each individual.
P1 is an example of the summary table for each individual.
Transactions:
The table contains one Header row, used to hold the labels for each column.
Columns A, B, C and D contain entered data.
The descriptions in column B cold be placed in a pop-up menu. I would suggest avoiding the … description. The description should give a reminder/memory jogger to the person entering the data which type of transaction (debit or credit) this is.
Each transaction is recorded on a separate row of the table. Expenses are recorded (as positive values) in column C; Income transactions are recorded (also as positive values) in column D. As each transaction is recorded on a separate line, no row will display amounts in both of these columns.
Debits are entered in column C as positive values. I used a 'red text' format as a visual clue that the values in this column are expenses.
Income items are entered in column D.
The rest of the columns in this table are calculated, using the formulas below.
E2, F2 and G2:
E2: ($D2−$C2)÷6
F2: ($D2−$C2)÷13.2
G2: ($D2−$C2)÷26.4
Each formula is filled down it's respective column to the bottom row of the table.
As column C OR column D will be empty in any given row, the first part of the formula will return either the amount in column D or the amount (as a negative number) in column C. Whichever is returned is multiplied (or in this case, divided) by the value applying to this person's share of income and expenses.
H2: SUM(E2,F2×2,G2×2)
This is obviously not the formula you have used, as yours returns the same amount as in column C or D of the same row. The sum here is of the share in this row of column E, twice the amount in column F and twice the amount in column G, following the perceived distribution implied in your original table.
P1:
The table summarizes the credit and debit amounts for Person 1, and calculates a running balance in column E.
The table has two header rows, one for the column labels, the second to hold the starting balance in column E and the identity of the person associated with the table in A1. (The content of A1 is not used in the formulas.)
Formulas:
A3: Transactions::A2
Filled right to B2, then both filled down to the bottom row of their respective columns.
Each formula returns the value from the cell one row above it in the same column of Transactions.
C3, D3
C3: IF(Transactions::E2<0,Transactions::E2,"")
D3: IF(Transactions::E2>0,Transactions::E2,"")
The first collects the debit values for this person into column C.
The second collects the credit values into column D.
0 values and empty cells return a null string to both columns.
E3: IF(LEN(C3&D3)<1,"",SUM($E$1,C$3:C3,D$3:D3))
The core part of the formula (shown in bold) sums the initial balance in E1, the negative) debit amounts to 'this row' of column C and the (positive) credit amounts to 'this row' of column D.
The rest of the formula (shown in normal type) measures the LENgth of the displayed values in 'this row' of columns C and D. IF the combined length (in characters) is less than 1, both are 'empty', and the formula returns a null string. If there is at least one character in one of the two columns, the SUM is clculated, and the current balance is returned to the cell.
Edits for each person:
The formulas in C3 and D3 must be edited for each person, changing the column (E) to the column containing the share information for 'this' person. Both formulas must then be filled down to the rest of their columns.
Regards,
Barry
