Showing Last number only
 

I have 5 columns of data:
A holds a date
B holds a dollar value
C holds another dollar value.
D holds is the sum of B and C
E holds a running total of D
so a sheet may look like this.

B C D E F
04/01 1.00 .50 1.50 1.50
04/02 2.00 1.00 3.50 5.00
04/03 5.00
04/04 1.00 1.00 2.00 7.00
and so on
Total 4.00 2.50 7.00 7.00
the last row shows a total of each column. except E
What I am looking to do is :
in E on the total line just show the last number.
I am able to do this now with this formula
"=LOOKUP(2,1/(F6:F36<0),F6:F36)"
however each blank row shows numbers That I do not want to show. Look at
04/03.
The formula in F is "f6+e7"
Also on the total row if no numbers appear above in F it gives me and N/A I
would like to show a 0.

I know I was able to do this a few years ago but can not find my notes on
how to accomplish it.

Please help.

Showing Last number only
 

Try a formula of

=IF(E9<0,MAX(F$6:F8)+E9,""

--

HTH

Bob

"Learning VBA" wrote in message
...
I have 5 columns of data:
A holds a date
B holds a dollar value
C holds another dollar value.
D holds is the sum of B and C
E holds a running total of D
so a sheet may look like this.

B C D E F
04/01 1.00 .50 1.50 1.50
04/02 2.00 1.00 3.50 5.00
04/03 5.00
04/04 1.00 1.00 2.00 7.00
and so on
Total 4.00 2.50 7.00 7.00
the last row shows a total of each column. except E
What I am looking to do is :
in E on the total line just show the last number.
I am able to do this now with this formula
"=LOOKUP(2,1/(F6:F36<0),F6:F36)"
however each blank row shows numbers That I do not want to show. Look at
04/03.
The formula in F is "f6+e7"
Also on the total row if no numbers appear above in F it gives me and N/A
I would like to show a 0.

I know I was able to do this a few years ago but can not find my notes on
how to accomplish it.

Please help.

Showing Last number only
 

This produced the following:

04/01 0 0 0 0
04/02 1 1 2 2
04/03 0 0 0
04/05 1 3 4 4
04/06 0 0 0
04/07 2 1 3 3
04/08 0 0 0
and so on
total 4 5 9 9


For 04/05 the last number should be 6
for 04/07 the last number should be 9
The last column where the formula is should be a running total. But if no
number is in column 2 3 and 4 last should be blank as is showing now..
The total should also be 9

"Bob Phillips" wrote in message
...
Try a formula of

=IF(E9<0,MAX(F$6:F8)+E9,""

--

HTH

Bob

"Learning VBA" wrote in message
...
I have 5 columns of data:
A holds a date
B holds a dollar value
C holds another dollar value.
D holds is the sum of B and C
E holds a running total of D
so a sheet may look like this.

B C D E F
04/01 1.00 .50 1.50 1.50
04/02 2.00 1.00 3.50 5.00
04/03 5.00
04/04 1.00 1.00 2.00 7.00
and so on
Total 4.00 2.50 7.00 7.00
the last row shows a total of each column. except E
What I am looking to do is :
in E on the total line just show the last number.
I am able to do this now with this formula
"=LOOKUP(2,1/(F6:F36<0),F6:F36)"
however each blank row shows numbers That I do not want to show. Look at
04/03.
The formula in F is "f6+e7"
Also on the total row if no numbers appear above in F it gives me and N/A
I would like to show a 0.

I know I was able to do this a few years ago but can not find my notes on
how to accomplish it.

Please help.

Showing Last number only
 

I had t correct the formula to add a trailing ) but this is what I got

04-Jan 0 0 0 0
04-Feb 1 1 2 2
04-Mar 0 0 0
04-May 1 3 4 6
04-Jun 0 0 0
04-Jul 2 1 3 9
04-Aug 0 0 0
and so on
total 4 5 9 9


--

HTH

Bob

"Learning VBA" wrote in message
...
This produced the following:

04/01 0 0 0 0
04/02 1 1 2 2
04/03 0 0 0
04/05 1 3 4 4
04/06 0 0 0
04/07 2 1 3 3
04/08 0 0 0
and so on
total 4 5 9 9


For 04/05 the last number should be 6
for 04/07 the last number should be 9
The last column where the formula is should be a running total. But if no
number is in column 2 3 and 4 last should be blank as is showing now..
The total should also be 9

"Bob Phillips" wrote in message
...
Try a formula of

=IF(E9<0,MAX(F$6:F8)+E9,""

--

HTH

Bob

"Learning VBA" wrote in message
...
I have 5 columns of data:
A holds a date
B holds a dollar value
C holds another dollar value.
D holds is the sum of B and C
E holds a running total of D
so a sheet may look like this.

B C D E F
04/01 1.00 .50 1.50 1.50
04/02 2.00 1.00 3.50 5.00
04/03 5.00
04/04 1.00 1.00 2.00 7.00
and so on
Total 4.00 2.50 7.00 7.00
the last row shows a total of each column. except E
What I am looking to do is :
in E on the total line just show the last number.
I am able to do this now with this formula
"=LOOKUP(2,1/(F6:F36<0),F6:F36)"
however each blank row shows numbers That I do not want to show. Look at
04/03.
The formula in F is "f6+e7"
Also on the total row if no numbers appear above in F it gives me and
N/A I would like to show a 0.

I know I was able to do this a few years ago but can not find my notes
on how to accomplish it.

Please help.

Showing Last number only
 

I also had to add the trailing ")". and this is the actual sheet

G H I J
R$ 0.00 0.00 R$ 0.00 R$ 0.00
0.00 0.00 0.00
R$ 5.00 R$ 1.00 R$ 4.00 R$ 4.00
0.00 0.00 0.00

R$ 20.00 0.00 R$ 20.00 R$ 20.00

This is the actual formula:
=IF(I10<0,MAX(J$5)+I10,"")

G - H = I J = running total

The 20.00 in the last column should be 24.00

"Bob Phillips" wrote in message
...
I had t correct the formula to add a trailing ) but this is what I got

04-Jan 0 0 0 0
04-Feb 1 1 2 2
04-Mar 0 0 0
04-May 1 3 4 6
04-Jun 0 0 0
04-Jul 2 1 3 9
04-Aug 0 0 0
and so on
total 4 5 9 9


--

HTH

Bob

"Learning VBA" wrote in message
...
This produced the following:

04/01 0 0 0 0
04/02 1 1 2 2
04/03 0 0 0
04/05 1 3 4 4
04/06 0 0 0
04/07 2 1 3 3
04/08 0 0 0
and so on
total 4 5 9 9


For 04/05 the last number should be 6
for 04/07 the last number should be 9
The last column where the formula is should be a running total. But if
no number is in column 2 3 and 4 last should be blank as is showing now..
The total should also be 9

"Bob Phillips" wrote in message
...
Try a formula of

=IF(E9<0,MAX(F$6:F8)+E9,""

--

HTH

Bob

"Learning VBA" wrote in message
...
I have 5 columns of data:
A holds a date
B holds a dollar value
C holds another dollar value.
D holds is the sum of B and C
E holds a running total of D
so a sheet may look like this.

B C D E F
04/01 1.00 .50 1.50 1.50
04/02 2.00 1.00 3.50 5.00
04/03 5.00
04/04 1.00 1.00 2.00 7.00
and so on
Total 4.00 2.50 7.00 7.00
the last row shows a total of each column. except E
What I am looking to do is :
in E on the total line just show the last number.
I am able to do this now with this formula
"=LOOKUP(2,1/(F6:F36<0),F6:F36)"
however each blank row shows numbers That I do not want to show. Look
at 04/03.
The formula in F is "f6+e7"
Also on the total row if no numbers appear above in F it gives me and
N/A I would like to show a 0.

I know I was able to do this a few years ago but can not find my notes
on how to accomplish it.

Please help.

Showing Last number only
 

You are not using the formula that I gave you, you stripped the MAX unction.
It should be

=IF(I10<0,MAX(J$5:J9)+I10,"")


--

HTH

Bob

"Learning VBA" wrote in message
...
I also had to add the trailing ")". and this is the actual sheet

G H I J
R$ 0.00 0.00 R$ 0.00 R$ 0.00
0.00 0.00 0.00
R$ 5.00 R$ 1.00 R$ 4.00 R$ 4.00
0.00 0.00 0.00

R$ 20.00 0.00 R$ 20.00 R$ 20.00

This is the actual formula:
=IF(I10<0,MAX(J$5)+I10,"")

G - H = I J = running total

The 20.00 in the last column should be 24.00

"Bob Phillips" wrote in message
...
I had t correct the formula to add a trailing ) but this is what I got

04-Jan 0 0 0 0
04-Feb 1 1 2 2
04-Mar 0 0 0
04-May 1 3 4 6
04-Jun 0 0 0
04-Jul 2 1 3 9
04-Aug 0 0 0
and so on
total 4 5 9 9


--

HTH

Bob

"Learning VBA" wrote in message
...
This produced the following:

04/01 0 0 0 0
04/02 1 1 2 2
04/03 0 0 0
04/05 1 3 4 4
04/06 0 0 0
04/07 2 1 3 3
04/08 0 0 0
and so on
total 4 5 9 9


For 04/05 the last number should be 6
for 04/07 the last number should be 9
The last column where the formula is should be a running total. But if
no number is in column 2 3 and 4 last should be blank as is showing
now..
The total should also be 9

"Bob Phillips" wrote in message
...
Try a formula of

=IF(E9<0,MAX(F$6:F8)+E9,""

--

HTH

Bob

"Learning VBA" wrote in message
...
I have 5 columns of data:
A holds a date
B holds a dollar value
C holds another dollar value.
D holds is the sum of B and C
E holds a running total of D
so a sheet may look like this.

B C D E F
04/01 1.00 .50 1.50 1.50
04/02 2.00 1.00 3.50 5.00
04/03 5.00
04/04 1.00 1.00 2.00 7.00
and so on
Total 4.00 2.50 7.00 7.00
the last row shows a total of each column. except E
What I am looking to do is :
in E on the total line just show the last number.
I am able to do this now with this formula
"=LOOKUP(2,1/(F6:F36<0),F6:F36)"
however each blank row shows numbers That I do not want to show. Look
at 04/03.
The formula in F is "f6+e7"
Also on the total row if no numbers appear above in F it gives me and
N/A I would like to show a 0.

I know I was able to do this a few years ago but can not find my notes
on how to accomplish it.

Please help.

Showing Last number only
 

You are correct I am sorry.
Thanks for you help.


"Bob Phillips" wrote in message
...
You are not using the formula that I gave you, you stripped the MAX
unction. It should be

=IF(I10<0,MAX(J$5:J9)+I10,"")


--

HTH

Bob

"Learning VBA" wrote in message
...
I also had to add the trailing ")". and this is the actual sheet

G H I J
R$ 0.00 0.00 R$ 0.00 R$ 0.00
0.00 0.00 0.00
R$ 5.00 R$ 1.00 R$ 4.00 R$ 4.00
0.00 0.00 0.00

R$ 20.00 0.00 R$ 20.00 R$ 20.00

This is the actual formula:
=IF(I10<0,MAX(J$5)+I10,"")

G - H = I J = running total

The 20.00 in the last column should be 24.00

"Bob Phillips" wrote in message
...
I had t correct the formula to add a trailing ) but this is what I got

04-Jan 0 0 0 0
04-Feb 1 1 2 2
04-Mar 0 0 0
04-May 1 3 4 6
04-Jun 0 0 0
04-Jul 2 1 3 9
04-Aug 0 0 0
and so on
total 4 5 9 9


--

HTH

Bob

"Learning VBA" wrote in message
...
This produced the following:

04/01 0 0 0 0
04/02 1 1 2 2
04/03 0 0 0
04/05 1 3 4 4
04/06 0 0 0
04/07 2 1 3 3
04/08 0 0 0
and so on
total 4 5 9 9


For 04/05 the last number should be 6
for 04/07 the last number should be 9
The last column where the formula is should be a running total. But if
no number is in column 2 3 and 4 last should be blank as is showing
now..
The total should also be 9

"Bob Phillips" wrote in message
...
Try a formula of

=IF(E9<0,MAX(F$6:F8)+E9,""

--

HTH

Bob

"Learning VBA" wrote in message
...
I have 5 columns of data:
A holds a date
B holds a dollar value
C holds another dollar value.
D holds is the sum of B and C
E holds a running total of D
so a sheet may look like this.

B C D E F
04/01 1.00 .50 1.50 1.50
04/02 2.00 1.00 3.50 5.00
04/03 5.00
04/04 1.00 1.00 2.00 7.00
and so on
Total 4.00 2.50 7.00 7.00
the last row shows a total of each column. except E
What I am looking to do is :
in E on the total line just show the last number.
I am able to do this now with this formula
"=LOOKUP(2,1/(F6:F36<0),F6:F36)"
however each blank row shows numbers That I do not want to show. Look
at 04/03.
The formula in F is "f6+e7"
Also on the total row if no numbers appear above in F it gives me and
N/A I would like to show a 0.

I know I was able to do this a few years ago but can not find my
notes on how to accomplish it.

Please help.