looking for simplified formulas

P

philip

I have formulas as following:
=(B1-A1)+(C1-B1)+(D1-C1)+(E1-D1)+..........+(AB1-AA1)

Is any one can help???

thanks
philip
 
D

Dana DeLouis

Re: looking for simplified formulas

Hi. Most of your variables cancell out.
This part:
=(B1-A1)+(C1-B1)+(D1-C1)+(E1-D1)
Reduces to
E1-A1

(Did you mean multiply? =(B1-A1) * (C1-B1) *...)
 
V

VN

Hi, Philip.

" =(B1-A1)+(C1-B1)+(D1-C1)+(E1-D1)+..........+(AB1-AA1)
"

above formula can rewrite as below:-

=AB1-A1


What is your meanning.. ?
Is something wrong in your post ?
 
P

philip

thank you for your reply, however, I need to explain a bit more, the
scenarios as follow

A B C D E F G CONT.....
2 4 6 7 9 11 23


the column will be continue to column AB

what I need is (col"B"-col"A")+(col"C"-col"B")...and so on...

the formula will be :=(B-A)+(C-B)+(D-C)+(E-D)-(F-F)+(G-F)....Ans is 19


thanks again
Philip
 
D

David Biddulph

philip said:
thank you for your reply, however, I need to explain a bit more, the
scenarios as follow

A B C D E F G CONT.....
2 4 6 7 9 11 23


the column will be continue to column AB

what I need is (col"B"-col"A")+(col"C"-col"B")...and so on...

the formula will be :=(B-A)+(C-B)+(D-C)+(E-D)-(F-F)+(G-F)....Ans is 19
Click to expand...

I assume you meant ... +(F-E) ..., rather than ... -(F-F) ...?

In which case the answer is 21 (not your 19), and (as has already been
pointed out) in the more general case, as the intermediate terms all cancel
out, the answer is the last column minus the first, or =(AB1-A1).
 
P

philip

thanks
however, how to setup a formula, so that , when I add the number in the last
column, it can be automatically use the last column to subtract the first
column, for example, column “G†is the last column, if I add the number in
column “Hâ€, the formula can automatically use the column “H†to subtract the
column “Aâ€

thanks again

philip
 
V

VN

you can be automatically use the last column to subtract the first
column by using this formula.

=INDEX(A1:AB1,MAX(IF(ISNUMBER(A1:AB1),COLUMN(A1:AB1))))-$A$1


Make sure that you press Ctrl+Shift+Enter for input array formula.


Hope this maybe useful.
VN.
 
Top