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

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

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  Register To Reply

2. ## Re: excel math forumla? (simple math problem inside for math people!)

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

"Jason" <jasonjm2005@hotmail.com> wrote in message
news:uPCdnUXdYJnl3WneRVn-jA@giganews.com...
> 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
>
>  Register To Reply

3. ## Re: excel math forumla? (simple math problem inside for math people!)

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)

"Jason" <jasonjm2005@hotmail.com> wrote in message
> 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
>
>
>
>
>
> "Jason" <jasonjm2005@hotmail.com> wrote in message
> news:uPCdnUXdYJnl3WneRVn-jA@giganews.com...
> > 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
> >
> >

>
>  Register To Reply

4. ## Re: excel math forumla? (simple math problem inside for math people!)

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).

guess?

HTH,
Bernd  Register To Reply