define FALSE,0
define TRUE,1

rem 
rem
rem SUMIF takes 3 parameters.
rem The source block
rem The condition
rem The destination block
rem
rem For each cell in the source, if the condition is met,
rem then the corresponding cell in the destination is added to the sum
rem 
rem E.G. If A1=5,A2=4,A3=6,A4=7 B1=8,B2=7,B3=1,B4=6
rem =SUMIF(A1:A4,">5",B1:B4) will = 7 because A3 and A4 match the 
rem condition, so B3 and B4 are summed.
rem
rem NOTE. The two blocks must be the same size and can be identical.
rem 
rem The condition are >,<,>=,<= If no condition symbol is uses, the
rem comparison is for identity.
rem The comparison need not be numeric.
rem =SUMIF(A1:A10,"Oranges",B1:B10) is ok.

macro SUMIF(a(),b,c())
FUNCMACRO
local res,i,j
res=0
i=first(a)-1
j=second(a)-1
if first(a)<>first(c) or second(a)<>second(c) then
  res="Blocks must be the same size"
else

for i=0 to first(a)-1
    for j=0 to second(a)-1
       if matched(a(i,j),b)=1 then res=res+c(i,j)
    next
next

endif
=res
endmacro


rem 
rem
rem COUNTIF takes 2 parameters.
rem The source block
rem The condition
rem
rem Counts the number of cells meeting the condition.
rem 
rem E.G. If A1=3,A2=4,A3=6,A4=7
rem =COUNTIF(A1:A4,">5") will = 2 because two cells are greater than 5
rem
rem The condition are >,<,>=,<= If no condition symbol is uses, the
rem comparison is for identity.
rem The comparison need not be numeric.
rem =COUNTIF(A1:A10,"Aardvark") is OK.


macro COUNTIF(a(),b)
FUNCMACRO
local res,i,j
res=0
i=first(a)-1
j=second(a)-1

for i=0 to first(a)-1
    for j=0 to second(a)-1
       if matched(a(i,j),b)=1 then res=res+1
    next
next
=res
endmacro


rem
rem matched is a macro used by SUMIF and COUNTIF

macro matched(x,c)
local res,i,j,k,l
res=FALSE
i=left(c,1)
if i="<" then
    if mid(c,2,1)="=" then
         if x<=mid(c,3,len(c)-2) then res=TRUE
    else
         if x<mid(c,3,len(c)-1) then res=TRUE
    endif

else

    if i=">" then
          if mid(c,2,1)="=" then
                  if x>=mid(c,3,len(c)-2) then res=TRUE
          else
                   if x>mid(c,3,len(c)-1) then res=TRUE
          endif
    else
          if x=c then res=TRUE
    endif
endif

=res
endmacro



rem
rem FACT calculates factorials
rem It uses a non recursive method
rem

macro FACT(n)
FUNCMACRO
local res,i
res=1
if n>1 then
   for i=1 to n
        res=res*i
   next
endif
=res
endmacro

rem
rem COMBIN calculates the number of combinations possible.
rem
rem E.g. =COMBIN(8,2) gives 28.
rem 

macro COMBIN(n,k)
FUNCMACRO
local res
n=int(n)
k=int(k)
if n<k or n<=0 or k<=0 then
   res="Invalid input to COMBIN"
else
   res=FACT(n)/(FACT(k)*FACT(n-k))
endif
=res
endmacro

rem
rem PERMUT calculates permutations
rem
rem E.g PERMUT(49,6) gives 10068347520

macro PERMUT(n,k)
FUNCMACRO
local res
n=int(n)
k=int(k)
if n<k or n<=0 or k<=0 then
  res="Invalid input to PERMUT"
else
  res=FACT(n)/FACT(n-k)
endif
=res
endmacro


rem
rem CORREL calculates the correlation coefficient of the data in the blocks
rem 
            
macro CORREL(a(),b())
FUNCMACRO
local res,sx,sy,sxsq,sysq,sxy,mx,my,n,i,direction

if first(a)>1 then 
    n=first(a) 
    direction=0
    if first(a)<>first(b) then ="Blocks must be the same size"
else 
    n=second(a)
    direction=1
    if second(a)<>second(b) then ="Blocks must be the same size"
endif

 

sx=0
sy=0
sxsq=0
sysq=0
for i=0 to n-1
  
       if direction = 0 then
              sx=sx+a(i,0)
              sxsq=sxsq+a(i,0)^2
              sy=sy+b(i,0)
              sysq=sysq+b(i,0)^2
              sxy=sxy+a(i,0)*b(i,0)
       else
              sx=sx+a(0,i)
              sxsq=sxsq+a(0,i)^2
              sy=sy+b(0,i)
              sysq=sysq+b(0,i)^2
              sxy=sxy+n(0,i)*a(0,i)

   endif
next

mx=sx/n
my=sy/n

=(sxy-n*(mx*my))/sqrt((sxsq-(n*mx^2))*(sysq-(n*my^2)))

endmacro

rem
rem RANK takes 3 parameters
rem value is the value to check
rem a() is the block of values
rem type is 0 for descending order, 1 for ascending.
rem
rem If A1=57,A2=34,A3=65,A4=57,A5=36
rem
rem =RANK(A1,A1:A5,0) = 2, because 57 is the second highest value
rem =RANK(A1,A1:A5,1) = 3, because 57 is the third lowest value
rem If the value is not in the list, the result is zero.

macro RANK(value,a(),type)
FUNCMACRO
local res,i,dummy,l(first(a),2)
res=0
for i=0 to first(a)-1
   l(i,0)=a(i)
   l(i,1)=i+1
next
dummy=shell_sort(l,first(a),type)
for i=0 to first(a)-1
   if i>0 and l(i,0)=l(i-1,0) then l(i,1)=l(i-1,1)
next

for i=0 to first(a)-1     
   if l(i,0)=value then res=l(i,1)  
next
=res
endmacro



macro shell_sort(a(),n,direction)
local gap,i,j,temp
gap=n/2
while (gap>0)
  for i=gap to n-1
     j=i-gap

if direction=0 then
     while (j>=0 and a(j,0)<a(j+gap,0))
           temp=a(j,0)
           a(j,0)=a(j+gap,0)
           a(j+gap,0)=temp
           j=j-gap
     endwhile
else
     while (j>=0 and a(j,0)>a(j+gap,0))
           temp=a(j,0)
           a(j,0)=a(j+gap,0)
           a(j+gap,0)=temp
           j=j-gap
     endwhile

endif


  next
  gap=int (gap/2)
endwhile
=0
endmacro


rem
rem ISBLANK takes a block as a parameter
rem If the top left cell is blank, the macro returns 1
rem otherwise it returns 0
rem NOTE this macro only works from Schema 2 1.04c onwards.
rem It has to take a block, rather than a cell unfortunately.
rem 

macro ISBLANK(a())
FUNCMACRO
local res,x,y
res=Cell(a,"CellType")
if res=4 then res=TRUE else res=FALSE
=res
endmacro



rem
rem ROUNDUP is like round, but always rounds up
rem 

macro ROUNDUP(n,d)
FUNCMACRO
local res,x,y
  if d=0 then 
     res=sign(n)*CEIL(ABS(n))
  else
      x=n*10^d
      y=x-int(x)
      x=int(x)
      if y>0 then x=x+1
      if y<0 then x=x-1
      res=x/(10^d)
  endif

=res
endmacro


rem
rem ROUNDDOWN is like round, but always rounds down.
rem 



macro ROUNDDOWN(n,d)
FUNCMACRO
local res,x,y

if d=0 then 
  res=Sign(n)*FLOOR(ABS(n))
else
  x=n*10^d
  y=x-int(x)
rem  if y>0 then x=x+1
  res=sign(n)*int(ABS(x))/(10^d)
endif
=res
endmacro


rem
rem EVEN rounds up to the next even integer.
rem It always rounds away from 0

macro EVEN(n)
FUNCMACRO
local res
res=CEIL(ABS(n))
if res MOD 2<>0 then res=res+1
res=res*Sign(n)
=res
endmacro


rem
rem ODD rounds up to the next odd integer.
rem It always rounds away from 0


macro ODD(n)
FUNCMACRO
local res
res=CEIL(ABS(n))
if res MOD 2=0 then res=res+1
res=res*Sign(n)
=res
endmacro


rem
rem IFF is similar to IF on Excel.
rem It can't be called IF here, because it clashes with the built in IF
rem the first parameter is the condition
rem if it evaluates as TRUE, the result is the second parameter
rem otherwise the result is the third parameter
rem E.G. =IF(A1<5,"Small","Large")
rem

macro IFF(a,b,c)
FUNCMACRO
local res
if a then res=b else res=c
=res
endmacro


rem 
rem SUMSQ returns the sum of the squares of the elements of the block
rem

macro SUMSQ(a())
FUNCMACRO
local res,i,j
res=0
for i=0 to first(a)-1
    for j=0 to second(a)-1
        res=res+a(i,j)^2
    next
next
=res
endmacro


rem
rem RMS returns the square root of the mean of the squares of the elements
rem of the array.
rem This isn't actually an Excel function, but it's useful and it's easy to write
rem when SUMSQ is available.
rem

macro RMS(a())
FUNCMACRO
local res,t
t=first(a)+second(a)
res=SQRT(SUMSQ(a)/t)
=res
endmacro



