Transfer running total to another cell

[pedro-egoli]

In column Z22:Z400 I have a total based on data in columns to the left of it.

They are in effect the running total of negatives and positives.

What formula can I use to transfer the last balance to cell E4 on same sheet.

Thanks

Pedro

 

[taigovinda]

In e4, try this formula:

=lookup(9.999E+307,z22:z400)

This will give you the bottom-most numerical value in the range in column Z. Is that what you are looking for?

 

[Big Monkey]

If it's a running total, doesn't that mean the last value will be the largest?

=max(Z22:Z400) ?

 

[taigovinda]

If it's a running total, doesn't that mean the last value will be the largest?

=max(Z22:Z400) ?

I wish my bank account worked that way

 

[SamTYler]

My method of maintaining a bank balance running total is easy

=0

 

[taigovinda]

Still trying to work my way back up to that!

 

[Big Monkey]

haha =]

 

[pedro-egoli]

Thanks Taigonvinda,
Works perfectly.

I wish it were the maximum but it goes up and down so need the latest .

Can you explain the 9.999E + 307 bit I had tried Lookup but got lost on what they wanted me to input.

Pedro

 

[taigovinda]

Aladin Aluryek has posted a couple of very good explanations to this that are detailed and insightful, but unfortunately I have only a minute and don't have the links handy - hopefully someone else provides, they are worth a good read.

In short, though, lookup() expects the array to be sorted ascending and returns the largest number that is equal to or less than the value you are looking for (so does match(,,1) as opposed to match(,,0); match could be used the same way). It looks through the list in column Z expecting that the lower it goes, the higher the number will be. So, when it is looking for a very huge number that it will never find, then what it does is go to the bottom of the list and, not finding the huge value you were looking for, it returns the last number in the list (it presumes that the last number is the largest one in the list, although your list is not sorted so that may not be true). Clear as mud?

 

[pedro-egoli]

Got the general drift and thanks for sparing the time.

Pedro