Excel, last entry in a column 2

[castelr]

I have a Excel question

Column A (Deposit) Column B (Withdrawals) Column C (Total)

Column C formula C5= C4+A5-B5

The last entry could be any where from c5 through c100. What would be the easiest way to find the last calculated number in column C to be able to show it on a total page?

 

[SkipVought]

Hi,

You really do not need to know the last column to get a total,
[tt]
=SUBTOTAL(9,Sheet1!C:C)
[/tt]
assuming that the column is on sheet1




Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[yogia]

Hi castelr:

You could also use the formula ...

=LOOKUP(9.9E+307,C:C)

to capture the last numeric entry in column C.

Theoretically, I should use 9.99999999999999E+307 (the largest possible number in EXCEL) rather than 9.9E+307 -- but this will do.

 

[SkipVought]

Actually I gave you the SUM parameter

To get the MAX value
[tt]
=SUBTOTAL(4,Sheet1!C:C)
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

But WHY are you putting a total in column C to begin with? Totals at the BOTTOM of a series of values is a vestage of paper, pencil and adding machine. With the summarizing functions available in Excel, your total can be placed in a location the the user need not hunt for it.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

I am trying to incorporate
=LOOKUP(9.9E+307,C:C)
and
=SUBTOTAL(9,Sheet1!C:C)


No luck, what is these formulas suppose to do ?

I am calculating Column Totals with non fixed lengh of the column

 

[SkipVought]

=SUBTOTAL(9,Sheet1!C:C)

Sums ALL the values in column C EXCEPT SUBTOTAL values

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

Yeah,
thanks ... it summarizes my column to 0.
I do have values in this column though...
Datatype is Number
One thing - my Worksheet has a name like 67A01
I wrote =SUBTOTAL(9,'67A01'!S:S)

 

[xlbo]

Data TYPE may LOOK like a number

try applying this formula to one of these "numeric" cells

=ISNUMBER(cell_ref)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ZoomDah]

No dear,
data is a number as (I am the one who created the table).

BTW I found this thread while looking in FAQ !
Am I good or what ?
I thing syntax could be wrong or something

Thanks and please, keep them coming

 

[bluedragon2]

Don't think the lookup solution will work since the totals are not sorted.

Not sure the MAX Subtotal will work since the last total may not be the largest.

Try:

=OFFSET(C5,COUNT(C5:C100)-1,0)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[mplee]

rather than look for the last total in column C (and off the top of my head i'm not sure how to do that, either) why not compute it separately?

if i understand the question correctly you could compute the final total by taking the initial balance and add all the deposits and subtract all withdrawals. thus your formula would look something like

TOTAL = C4+SUM(A5:A100)-SUM(B5:B100)

best of luck!

** mp **