Formula help
 

I haven't used Excel in a while. I am trying to figure out how to create a
formula to display the result of subtracting different cells in a row. For
example:
n=a1-b1 with result showing in n; then when I update the next time I want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to do
this???

Thanks in advance!

Formula help
 

In Cell B2, type

=A1-B1

then copy cell B2 and paste to C2 and so on, to match your last cell in row
1

HTH,
Bernie
MS Excel MVP


"bpa" wrote in message
...
I haven't used Excel in a while. I am trying to figure out how to create a
formula to display the result of subtracting different cells in a row.
For
example:
n=a1-b1 with result showing in n; then when I update the next time I want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to do
this???

Thanks in advance!

Formula help
 

Hi Bernie,
Thank you for answering. Maybe I'm not understanding, or maybe not clear
enough on what I am looking for. I need the result from the calculation to
be on the same row, i.e. N1 and for the number in N1 to change as I add in
additional values in the same row. I tried your idea and the formatting
flowed properly, but when I added data in the cells going across, the
formatting disappears and the calculations no longer work?? I can always
change the formula in the N column and copy it down to the remainder of the
rows, but was hoping for an "automated" way!! Maybe wishful thinking on my
part? Thanks again!
Brenda

"Bernie Deitrick" wrote:

In Cell B2, type

=A1-B1

then copy cell B2 and paste to C2 and so on, to match your last cell in row
1

HTH,
Bernie
MS Excel MVP


"bpa" wrote in message
...
I haven't used Excel in a while. I am trying to figure out how to create a
formula to display the result of subtracting different cells in a row.
For
example:
n=a1-b1 with result showing in n; then when I update the next time I want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to do
this???

Thanks in advance!

Formula help
 

In O1 copy and paste this:
=IF(AND(ISBLANK(C1),ISBLANK(D1),ISBLANK(E1),ISBLAN K(F1),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK( J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),B1-A1,IF(AND(ISBLANK(D1),ISBLANK(E1),ISBLANK(F1),ISBL ANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLAN K(K1),ISBLANK(L1),ISBLANK(M1)),C1-B1,IF(AND(ISBLANK(E1),ISBLANK(F1),ISBLANK(G1),ISBL ANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLAN K(L1),ISBLANK(M1)),D1-C1,IF(AND(ISBLANK(F1),ISBLANK(G1),ISBLANK(H1),ISBL ANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLAN K(M1)),E1-D1,IF(AND(ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBL ANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),F1-E1,IF(AND(ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBL ANK(K1),ISBLANK(L1),ISBLANK(M1)),G1-F1))))))

Name cell O1: Part1

In P1, copy and paste this:
=IF(AND(ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLAN K(L1),ISBLANK(M1)),H1-G1,IF(AND(ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBL ANK(M1)),I1-H1,IF(AND(ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),J1-I1,IF(AND(ISBLANK(L1),ISBLANK(M1)),K1-J1,IF(AND(ISBLANK(M1)),L1-K1,M1-L1)))))

Name cell P1: Part2

In N1 type: =IF(Part1,Part1,Part2)

That should do it.

"bpa" wrote:

Hi Bernie,
Thank you for answering. Maybe I'm not understanding, or maybe not clear
enough on what I am looking for. I need the result from the calculation to
be on the same row, i.e. N1 and for the number in N1 to change as I add in
additional values in the same row. I tried your idea and the formatting
flowed properly, but when I added data in the cells going across, the
formatting disappears and the calculations no longer work?? I can always
change the formula in the N column and copy it down to the remainder of the
rows, but was hoping for an "automated" way!! Maybe wishful thinking on my
part? Thanks again!
Brenda

"Bernie Deitrick" wrote:

In Cell B2, type

=A1-B1

then copy cell B2 and paste to C2 and so on, to match your last cell in row
1

HTH,
Bernie
MS Excel MVP


"bpa" wrote in message
...
I haven't used Excel in a while. I am trying to figure out how to create a
formula to display the result of subtracting different cells in a row.
For
example:
n=a1-b1 with result showing in n; then when I update the next time I want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to do
this???

Thanks in advance!

Formula help
 

Hi Brenda,

Do you mean that the formula in N1 will always subtract
the last two values entered in A1 to M1.

If that is the case then try this in N1.
=INDEX(A1:M1,LARGE(IF(LEN(A1:M1)0,COLUMN(A1:M1)), 2))-LOOKUP(1E+100,A1:M1)

That is an array formula and must be comitted by using
Ctrl+Shft+Enter and not just Enter.

As you enter values across row 1 it will update and
only subtract the last two values.

HTH
Martin


"bpa" wrote in message
...
Hi Bernie,
Thank you for answering. Maybe I'm not understanding, or maybe not clear
enough on what I am looking for. I need the result from the calculation
to
be on the same row, i.e. N1 and for the number in N1 to change as I add in
additional values in the same row. I tried your idea and the formatting
flowed properly, but when I added data in the cells going across, the
formatting disappears and the calculations no longer work?? I can always
change the formula in the N column and copy it down to the remainder of
the
rows, but was hoping for an "automated" way!! Maybe wishful thinking on
my
part? Thanks again!
Brenda

"Bernie Deitrick" wrote:

In Cell B2, type

=A1-B1

then copy cell B2 and paste to C2 and so on, to match your last cell in
row
1

HTH,
Bernie
MS Excel MVP


"bpa" wrote in message
...
I haven't used Excel in a while. I am trying to figure out how to
create a
formula to display the result of subtracting different cells in a row.
For
example:
n=a1-b1 with result showing in n; then when I update the next time I
want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to
do
this???

Thanks in advance!

Formula help
 

On Fri, 1 Aug 2008 15:15:01 -0700, bpa wrote:

I haven't used Excel in a while. I am trying to figure out how to create a
formula to display the result of subtracting different cells in a row. For
example:
n=a1-b1 with result showing in n; then when I update the next time I want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to do
this???

Thanks in advance!


If I understand you correctly, you will be entering numbers sequentially in A1,
B1, C1 etc.

You want the subtraction of the last number entered from the next-to-last
number entered to appear in N1.

I assume the last value would be entered in M1.

That being the case, try this formula:

=OFFSET(N1,0,COUNT(A1:M1)-COLUMN(N1)-1)-
OFFSET(N1,0,COUNT(A1:M1)-COLUMN(N1))

--ron

Formula help
 

Thank you - this approach worked!!
Brenda

"Guy Lydig" wrote:

In O1 copy and paste this:
=IF(AND(ISBLANK(C1),ISBLANK(D1),ISBLANK(E1),ISBLAN K(F1),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK( J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),B1-A1,IF(AND(ISBLANK(D1),ISBLANK(E1),ISBLANK(F1),ISBL ANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLAN K(K1),ISBLANK(L1),ISBLANK(M1)),C1-B1,IF(AND(ISBLANK(E1),ISBLANK(F1),ISBLANK(G1),ISBL ANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLAN K(L1),ISBLANK(M1)),D1-C1,IF(AND(ISBLANK(F1),ISBLANK(G1),ISBLANK(H1),ISBL ANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLAN K(M1)),E1-D1,IF(AND(ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBL ANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),F1-E1,IF(AND(ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBL ANK(K1),ISBLANK(L1),ISBLANK(M1)),G1-F1))))))

Name cell O1: Part1

In P1, copy and paste this:
=IF(AND(ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLAN K(L1),ISBLANK(M1)),H1-G1,IF(AND(ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBL ANK(M1)),I1-H1,IF(AND(ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),J1-I1,IF(AND(ISBLANK(L1),ISBLANK(M1)),K1-J1,IF(AND(ISBLANK(M1)),L1-K1,M1-L1)))))

Name cell P1: Part2

In N1 type: =IF(Part1,Part1,Part2)

That should do it.

"bpa" wrote:

Hi Bernie,
Thank you for answering. Maybe I'm not understanding, or maybe not clear
enough on what I am looking for. I need the result from the calculation to
be on the same row, i.e. N1 and for the number in N1 to change as I add in
additional values in the same row. I tried your idea and the formatting
flowed properly, but when I added data in the cells going across, the
formatting disappears and the calculations no longer work?? I can always
change the formula in the N column and copy it down to the remainder of the
rows, but was hoping for an "automated" way!! Maybe wishful thinking on my
part? Thanks again!
Brenda

"Bernie Deitrick" wrote:

In Cell B2, type

=A1-B1

then copy cell B2 and paste to C2 and so on, to match your last cell in row
1

HTH,
Bernie
MS Excel MVP


"bpa" wrote in message
...
I haven't used Excel in a while. I am trying to figure out how to create a
formula to display the result of subtracting different cells in a row.
For
example:
n=a1-b1 with result showing in n; then when I update the next time I want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to do
this???

Thanks in advance!

Formula help
 

Thank you, Martin!

"MartinW" wrote:

Hi Brenda,

Do you mean that the formula in N1 will always subtract
the last two values entered in A1 to M1.

If that is the case then try this in N1.
=INDEX(A1:M1,LARGE(IF(LEN(A1:M1)0,COLUMN(A1:M1)), 2))-LOOKUP(1E+100,A1:M1)

That is an array formula and must be comitted by using
Ctrl+Shft+Enter and not just Enter.

As you enter values across row 1 it will update and
only subtract the last two values.

HTH
Martin


"bpa" wrote in message
...
Hi Bernie,
Thank you for answering. Maybe I'm not understanding, or maybe not clear
enough on what I am looking for. I need the result from the calculation
to
be on the same row, i.e. N1 and for the number in N1 to change as I add in
additional values in the same row. I tried your idea and the formatting
flowed properly, but when I added data in the cells going across, the
formatting disappears and the calculations no longer work?? I can always
change the formula in the N column and copy it down to the remainder of
the
rows, but was hoping for an "automated" way!! Maybe wishful thinking on
my
part? Thanks again!
Brenda

"Bernie Deitrick" wrote:

In Cell B2, type

=A1-B1

then copy cell B2 and paste to C2 and so on, to match your last cell in
row
1

HTH,
Bernie
MS Excel MVP


"bpa" wrote in message
...
I haven't used Excel in a while. I am trying to figure out how to
create a
formula to display the result of subtracting different cells in a row.
For
example:
n=a1-b1 with result showing in n; then when I update the next time I
want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to
do
this???

Thanks in advance!

Formula help
 

Thank you!

"Ron Rosenfeld" wrote:

On Fri, 1 Aug 2008 15:15:01 -0700, bpa wrote:

I haven't used Excel in a while. I am trying to figure out how to create a
formula to display the result of subtracting different cells in a row. For
example:
n=a1-b1 with result showing in n; then when I update the next time I want
n=b1-c1
and so on for the remainder of the cells in row 1. Is there a way to do
this???

Thanks in advance!


If I understand you correctly, you will be entering numbers sequentially in A1,
B1, C1 etc.

You want the subtraction of the last number entered from the next-to-last
number entered to appear in N1.

I assume the last value would be entered in M1.

That being the case, try this formula:

=OFFSET(N1,0,COUNT(A1:M1)-COLUMN(N1)-1)-
OFFSET(N1,0,COUNT(A1:M1)-COLUMN(N1))

--ron