|
PureBytes Links
Trading Reference Links
|
Nothing like an Excel challenge to get the gears in motion on a Saturday
morning!
Assuming your data series begins in A1 and runs down the column, the
following formula should be placed in B4 and pasted down.
=SUM(IF(A1>=1,A1,-(1-A1)),IF(A2>=1,A2,-(1-A2)),IF(A3>=1,A3,-(1-A3)),IF(A4>=1,A4,-(1-A4)))
If you have an exceptionally large data set, it might speed up the
calculations if you place the following in B1 and paste down:
=IF(A1>=1,A1,-(1-A1))
and then put the following in C4 and paste down.
=SUM(B1:B4)
Hope this helps!
Bob Hunt
THE PATTERN TRAPPER
E-Mail: RHunt@xxxxxxxxxxxxxxxxxx
Web Site: http://www.PatternTrapper.com
Phone: 612-892-5550
------------------------------------------------------------------
Proffittak@xxxxxxx wrote:
>
> hi
>
> for the experts out there
>
> how do i tell excel to do the following
>
> if the number is 1 or larger add to the total of last 4
> if the number is UNDER 1 then add : 1 minus the fraction( and with a
> MINUS)
>
> example 2.1 +1.9 +. 37+ . 69 the
> answer will be 3.06
>
> because .37 is =to (-.63) and .69 =(-.31)
>
> thanks in advance
> Ben
|