[SOLVED]: Make cell echo last entry in a column range?

[kharrisma]

Hey Forum Folke,

I'm trying to get a "Totals" cell (G10) to 'echo' the last entry in a column range (G3 through G6). There are four rows, each with either a non-zero dollar amount, or $0.00. The column adds each row as entries are made, then sums the amounts in this cell I'm asking about. I don't want the cell to sum the column's entries; I want it to just echo the last entry in the column range, and ignore prior entries. Is this possible? I'm attaching a few screenshots to illustrate:

The actual account balance is $51.48, not the $78.49 displayed in the cell. I'd like (G10) to just "echo" the last entry ($51.48) in the G column, to reflect the actual total, not the total from the week prior PLUS the current week. It needs to do this for each row; echo only the last non-zero entry. I've dug around in the help section but I've no idea what that function might be called, so I'm not having much luck. Any help would be greatly appreciated!!

[FJCC]

This formula will return the last value in the range B2:B6.

Code: Select all

=INDEX(B2:B6;COUNT(B2:B6))

A similar one may work for you, depending on what is in the cells in your image that currently show FALSE

[kharrisma]

@FJCC: Thanks for your fast reply. Below is a screenshot of what happens when I insert your expression in the G10 cell (changed it to reflect the G3:G6 range I'm working with):

The cells in the G4:G6 range contain the following expression: =IF((F5>0);(SUM(F5;G4))) . This looks at the cell to the left, and if it contains a non-zero value, it adds that value to the cell above the G cell with this expression; i.e, G5 looks at F5; if F5 is greater than zero, it then sums G4 and F5. I don't know if this expression will skew your expression or not.

Here's another screenshot showing the expression in one of the cells:

Ideally, I'd like the cell (G4, or G5, or G6) to show nothing at all if the value in the F column cell is $0.00, but I was so tickled to get it to work at all that I just went with it.

Any other ideas, or a clue where I should be looking? Thanks again!

[FJCC]

I changed my column B to have a formula like

Code: Select all

=IF(A3>0;B2+A3;"")

That will return the sum of the cell above and to the left if the cell to the left has a value > 0. Otherwise it returns an empty text string, which makes the cell look empty. The structure of the IF() function is

Code: Select all

IF(Test Condition; Return this if Test Condition is TRUE; Return this if Test Condition is FALSE)

You left the last part blank so the function just returns the result of the Test, which is FALSE. FALSE has the value zero and that is why my original formula was showing a zero.

[kharrisma]

Hey FJCC,

You're a life-saver so far; the monthly sheets work fine; the annual summary sheet's acting weird, though; same formulae, different result.

This is the revised formula you showed me to cause the cell to display blank if the cell to the left is zero; works great.

Here's the formula you gave me that causes the cell to echo the last entry in the column's range; again, works perfectly. I've got an idea what I'm doing now (or so I thought....)

Here's the Annual Summary sheet; you can see I'm using the same formula to get the cell to display blank if there's a zero value in the cells to the left.

And here's the final total, that should be echoing the last entry in the column, but instead it's showing blank. The top cell in the column is not included in the working range of the formula, as it's just an =cell next to it, which would probably foul up the formula, so I omitted it. As far as I've been able to tell, everything's the same as the monthly sheet, but it isn't working the same. Found a syntax error (; instead of : in the COUNT range ), but correcting that didn't change anything. Can you tell what I'm doing wrong??

Hold on... ignore this post until I can fix the screenshots... got the wrong ones in there somehow. I'll be back

[FJCC]

The formula in H19 is returning a blank because only two cells in the range H7:H17 contain numbers and those cells are not at the top of the range.

This is how INDEX() works in this particular context.

Code: Select all

INDEX(RANGE; ROWS)

INDEX() works with the range of cells entered where I have written RANGE and it returns the value that is in the ROWS position. So if RANGE is H7:H17 and ROWS is equal to 2, INDEX() will return the value in H8, the second position.

COUNT() tells you how many cells in the given range contain numbers. In your case COUNT(H7:H17) will return 2. So,

Code: Select all

INDEX(H7:H17;COUNT(H7:H17))

will return what is in H8, which is either an empty cell or a cell that contains the empty text "". To get the formula in H19 to work, you have to have zeros in H7:H13.

[kharrisma]

BINGO! Thanks so much for your help!!

[lader]

Last non-empty value:

Code: Select all

{=INDEX(H6:H17; MAX(IF((H6:H17)<>""; ROW(H6:H17)-ROW(H6)+1; 1)))}

Enter it as matrix formula with Shift+Ctrl+Enter