Formula help

  • Thread starter Thread starter bpa
  • Start date Start date
B

bpa

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!
 
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
 
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
 
In O1 copy and paste this:
=IF(AND(ISBLANK(C1),ISBLANK(D1),ISBLANK(E1),ISBLANK(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),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),C1-B1,IF(AND(ISBLANK(E1),ISBLANK(F1),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),D1-C1,IF(AND(ISBLANK(F1),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),E1-D1,IF(AND(ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),F1-E1,IF(AND(ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(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),ISBLANK(L1),ISBLANK(M1)),H1-G1,IF(AND(ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(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.
 
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
 
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!
Click to expand...


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
 
Thank you - this approach worked!!
Brenda

Guy Lydig said:
In O1 copy and paste this:
=IF(AND(ISBLANK(C1),ISBLANK(D1),ISBLANK(E1),ISBLANK(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),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),C1-B1,IF(AND(ISBLANK(E1),ISBLANK(F1),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),D1-C1,IF(AND(ISBLANK(F1),ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),E1-D1,IF(AND(ISBLANK(G1),ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(M1)),F1-E1,IF(AND(ISBLANK(H1),ISBLANK(I1),ISBLANK(J1),ISBLANK(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),ISBLANK(L1),ISBLANK(M1)),H1-G1,IF(AND(ISBLANK(J1),ISBLANK(K1),ISBLANK(L1),ISBLANK(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.
Click to expand...
 
Thank you, Martin!

MartinW said:
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
Click to expand...
 
Thank you!

Ron Rosenfeld said:
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
Click to expand...
 
Back
Top