excel math forumla? (simple math problem inside for math people!)

J

Jason

Hey all I got a math problem I am not sure how to put into a formula nor an
excel formula

here is the nature of the answer I need

lets say we have X priced at 100

every 1 it moves I will add 1 position

so at 101 it will add a position
102 another position
103 another

at the end of the day lets say the item closes at 106 and 106 is also the
high for the day

the profit should then be 106-100 + 105-100 etc etc
yielding a total profit of

6+5+4+3+2+1 = 21

anyone know how to encode this all into excel? hope I made sense
 
J

Jason

wooops

if it closed at 106 my profit would in fact be

5 + 4 + 3 + 2 + 1 = 15

coz 1st position would only open at 101
 
B

Bob Phillips

If start is A1, 1st position is A2, then try

=SUM(IF(A3:A10<>"",A3:A10-A2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
B

bplumhoff

Hi Jason,

Put your start value into A1 and your end value into B1, then
=SIGN(B1-A1)*(B1-A1)*(B1-A1-SIGN(B1-A1))/2
will give you what you want (I hope).

If you start with 106 and end at 100 then your result will be -15, I
guess?

HTH,
Bernd
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top