module ExcelFunctions

Public Instance Methods

_filtered_range(range, *criteria) click to toggle source
# File src/excel/excel_functions/sumifs.rb, line 3
def _filtered_range(range, *criteria)
  # Sort out the sum range
  range = [range] unless range.is_a?(Array)
  range = range.flatten

  indexes = _filtered_range_indexes(range, *criteria)
  return indexes if indexes.is_a?(Symbol)
  range.values_at(*indexes)
end
_filtered_range_indexes(range, *criteria) click to toggle source
# File src/excel/excel_functions/sumifs.rb, line 13
def _filtered_range_indexes(range, *criteria)
  # Sort out the criteria
  0.step(criteria.length-1,2).each do |i|
    if criteria[i].is_a?(Array)
      criteria[i] = criteria[i].flatten
    else
      criteria[i] = [criteria[i]]
    end
  end

  filtered = []

  # Work through each part of the sum range
  range.each_with_index do |potential,index|
    #next unless potential.is_a?(Numeric)

    # If a criteria fails, this is set to false and no further criteria are evaluated
    pass = true

    0.step(criteria.length-1,2).each do |i|
      check_range = criteria[i]
      required_value = criteria[i+1] || 0
      if required_value.is_a?(String)
        any_start = required_value.start_with?('*')
        any_end = required_value.end_with?('*')
        if any_start || any_end
          required_value = ".*"+required_value[1..-1] if any_start
          required_value = required_value[0..-2]+".*" if any_end
          required_value = /^#{required_value}$/i
        end
      end
      return :value if index >= check_range.length
      check_value = check_range[index]

      pass = case check_value
      when String
        case required_value
        when String
          check_value.downcase == required_value.downcase
        when Regexp
          check_value =~ required_value
        when Numeric
          begin
            Float(check_value) == required_value.to_f
          rescue ArgumentError
            false
          end
        else
          check_value.downcase == required_value.to_s.downcase
        end
      when true, false
        check_value == required_value
      when nil
        required_value == ""
      when Numeric
        case required_value
        when Numeric
          check_value == required_value
        when String
          required_value =~ /^\s*(<=|>=|<|>)?\s*([-+]?[0-9]+\.?[0-9]*([eE][-+]?[0-9]+)?)\s*$/
          if $1 && $2
            check_value.send($1,$2.to_f)
          elsif $2
            check_value == $2.to_f
          else
            false
          end
        else
          check_value == required_value
        end
      when Symbol
        check_value == required_value
      end # case check_value

      break unless pass
    end # criteria loop

    filtered << index if pass
  end

  return filtered
end
abs(a) click to toggle source
# File src/excel/excel_functions/abs.rb, line 3
def abs(a)
  case a
  when Numeric; return a.abs
  when Symbol; return a
  when nil; return 0
  when true; return 1
  when false; return 0
  else; return :value
  end
end
add(a,b) click to toggle source
# File src/excel/excel_functions/add.rb, line 6
def add(a,b)
  # return apply_to_range(a,b) { |a,b| add(a,b) } if a.is_a?(Array) || b.is_a?(Array)

  a = number_argument(a)
  b = number_argument(b)

  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  
  a + b
end
address(row_num, column_num, abs_num = 1, a1 = true, sheet_text = nil) click to toggle source

ADDRESS(row_num,column_num,abs_num,a1,sheet_text) row_num The row number to use in the cell reference. column_num The column number to use in the cell reference. abs_num Specifies the type of reference to return. 1 or omitted returns the following type of reference: absolute. 2 returns the following type of reference: absolute row; relative column. 3 returns the following type of reference: relative row; absolute column. 4 returns the following type of reference: relative. a1 A logical value that specifies the A1 or R1C1 reference style. If a1 is TRUE or omitted, ADDRESS returns an A1-style reference. If a1 is FALSE, ADDRESS returns an R1C1-style reference. sheet_text Text specifying the name of the sheet to be used as the external reference. If sheet_text is omitted, no sheet name is used.

# File src/excel/excel_functions/address.rb, line 21
def address(row_num, column_num, abs_num = 1, a1 = true, sheet_text = nil)
  raise NotSupportedException.new("address() function R1C1 references not implemented") if a1 == false

  return row_num if row_num.is_a?(Symbol)
  return column_num if column_num.is_a?(Symbol)
  return abs_num if abs_num.is_a?(Symbol)
  return a1 if a1.is_a?(Symbol)
  return sheet_text if sheet_text.is_a?(Symbol)
  
  row_num = number_argument(row_num)
  column_num = number_argument(column_num)
  abs_num = number_argument(abs_num)
  
  return :value if row_num < 1
  return :value if column_num < 1
  return :value if abs_num < 1
  return :value if abs_num > 4
  
  row = row_num.to_i.to_s
  column = Reference.column_letters_for_column_number[column_num.to_i].to_s
  
  ref = case abs_num
  when 1; "$"+column+"$"+row
  when 2; "" +column+"$" +row
  when 3; "$"+column+"" +row
  when 4;  ""+column+"" +row
  end
  
  if sheet_text
    ref = "'#{sheet_text}'!"+ref
  end
  
  ref

end
apply_to_range(a,b) { |cell, b[j]| ... } click to toggle source
# File src/excel/excel_functions/apply_to_range.rb, line 3
def apply_to_range(a,b)
  a = Array.new(b.length,Array.new(b.first.length,a)) unless a.is_a?(Array)
  b = Array.new(a.length,Array.new(a.first.length,b)) unless b.is_a?(Array)
  
  return :value unless b.length == a.length
  return :value unless b.first.length == a.first.length
  
  a.map.with_index do |row,i|
    row.map.with_index do |cell,j|
      yield cell, b[i][j]
    end
  end
end
average(*args) click to toggle source
# File src/excel/excel_functions/average.rb, line 3
def average(*args)
  args = args.flatten
  error = args.find {|a| a.is_a?(Symbol)}
  return error if error
  args.delete_if { |a| !a.is_a?(Numeric) }
  return :div0 if args.empty?
  args.inject(0.0) { |m,i| m + i.to_f } / args.size.to_f
end
averageifs(average_range, *criteria) click to toggle source

See sumifs.rb for _filtered_range

# File src/excel/excel_functions/averageifs.rb, line 5
def averageifs(average_range, *criteria)
  filtered = _filtered_range(average_range, *criteria)
  average(*filtered)
end
ceiling(number, multiple, mode = 0) click to toggle source
# File src/excel/excel_functions/ceiling.rb, line 3
def ceiling(number, multiple, mode = 0)
  return number if number.is_a?(Symbol)
  return multiple if multiple.is_a?(Symbol)
  return mode if mode.is_a?(Symbol)
  number = number_argument(number)
  multiple = number_argument(multiple)
  mode = number_argument(mode)
  return :value unless number.is_a?(Numeric)
  return :value unless multiple.is_a?(Numeric)
  return :value unless mode.is_a?(Numeric)
  return 0 if multiple == 0
  if mode == 0 || number > 0
    whole, remainder = number.divmod(multiple)
    num_steps = remainder > 0 ? whole + 1 : whole
    num_steps * multiple
  else # Need to round negative away from zero
    -ceiling(-number, multiple, 0)
  end
end
cell(info_type, reference = nil) click to toggle source
# File src/excel/excel_functions/cell.rb, line 3
def cell(info_type, reference = nil)
  return info_type if info_type.is_a?(Symbol)
  return :value unless info_type.is_a?(String)
  case info_type.downcase
  when 'filename'
    original_excel_filename
  else
    :value
  end
end
char(a) click to toggle source
# File src/excel/excel_functions/char.rb, line 3
def char(a)
  a = number_argument(a)
  return a if a.is_a?(Symbol)
  return :value if a <= 0
  return :value if a >= 256
  a = a.floor
  "".force_encoding("Windows-1252") << a
end
choose(index,*args) click to toggle source
# File src/excel/excel_functions/choose.rb, line 3
def choose(index,*args)
  # If an argument is an error, return that
  return index if index.is_a?(Symbol)
  error = args.find {|a| a.is_a?(Symbol)}
  return error if error
  
  # If the index is out of bounds, return an error
  return :value unless index
  return :value if index < 1
  return :value if index > args.length
  
  return args[index-1] || 0
  
end
cosh(x) click to toggle source
# File src/excel/excel_functions/cosh.rb, line 3
def cosh(x)
  return x if x.is_a?(Symbol)
  x ||= 0
  Math.cosh(x)
end
count(*args) click to toggle source
# File src/excel/excel_functions/count.rb, line 3
def count(*args)
  args = args.flatten
  args.delete_if { |a| !a.is_a?(Numeric)}
  args.size
end
counta(*args) click to toggle source
# File src/excel/excel_functions/counta.rb, line 3
def counta(*args)
  args = args.flatten.compact
  args.size
end
countif(count_range, criteria) click to toggle source
# File src/excel/excel_functions/countif.rb, line 3
def countif(count_range, criteria)
  return 0 if criteria.kind_of?(Array) # Who knows why, Excel
  count_range = [count_range] unless count_range.kind_of?(Array)
  rows = count_range.size
  if count_range.first && count_range.first.kind_of?(Array)
      sum_range = Array.new(rows, Array.new(count_range.first.size,1))
  else
      sum_range = Array.new(rows, 1)
  end
  return sumif(count_range, criteria, sum_range)
end
countifs(*criteria) click to toggle source
# File src/excel/excel_functions/countifs.rb, line 3
def countifs(*criteria)
  return 0 if criteria.empty?
  c = criteria[0].is_a?(Array) ? criteria[0] : [criteria[0]]
  count = c.map { 1 }
  return sumifs(count, *criteria)
end
curve(curveType, currentYear, startValue, endValue, duration, startYear = 2018) click to toggle source
# File src/excel/excel_functions/scurve.rb, line 64
def curve(curveType, currentYear, startValue, endValue, duration, startYear = 2018)
  if curveType == "s"
    return scurve(currentYear, startValue, endValue, duration, startYear)
  elsif curveType == "hs"
    return halfscurve(currentYear, startValue, endValue, duration, startYear)
  else
    return lcurve(currentYear, startValue, endValue, duration, startYear)
  end
end
divide(a,b) click to toggle source
# File src/excel/excel_functions/divide.rb, line 6
def divide(a,b)
  # return apply_to_range(a,b) { |a,b| divide(a,b) } if a.is_a?(Array) || b.is_a?(Array)

  a = number_argument(a)
  b = number_argument(b)

  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  
  return :div0 if b == 0
  
  a / b.to_f

rescue ZeroDivisionError
  :div0
end
ensure_is_number(a) click to toggle source
# File src/excel/excel_functions/ensure_is_number.rb, line 3
def ensure_is_number(a)
  return number_argument(a)
end
excel_and(*args) click to toggle source
# File src/excel/excel_functions/and.rb, line 3
def excel_and(*args)
  # Flatten arrays
  args = args.flatten
  
  # If an argument is an error, return that
  error = args.find {|a| a.is_a?(Symbol)}
  return error if error
  
  # Replace 1 and 0 with true and false
  args.map! do |a|
    case a
    when 1; true
    when 0; false
    else; a
    end
  end
  
  # Remove anything not boolean
  args.delete_if { |a| !(a.is_a?(TrueClass) || a.is_a?(FalseClass)) }
  
  # Return an error if nothing less
  return :value if args.empty?
  
  # Now calculate and return
  args.all? {|a| a == true }
end
excel_equal?(a,b) click to toggle source
# File src/excel/excel_functions/excel_equal.rb, line 5
def excel_equal?(a,b)
  return apply_to_range(a,b) { |a,b| excel_equal?(a,b) } if a.is_a?(Array) || b.is_a?(Array)
  
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  
  return a.downcase == b.downcase if a.is_a?(String) && b.is_a?(String)
  a == b
  
end
excel_if(condition,true_value,false_value = false) click to toggle source
# File src/excel/excel_functions/excel_if.rb, line 3
def excel_if(condition,true_value,false_value = false)
  return condition if condition.is_a?(Symbol)
  return false_value if condition == 0
  condition ? true_value : false_value
end
excel_match(lookup_value,lookup_array,match_type = 1) click to toggle source

FIXME: Excel doesn't use this algorithm for matching. Seems to do some bisecting. Only matters if doing an ordered search and the range is not in order

# File src/excel/excel_functions/excel_match.rb, line 6
def excel_match(lookup_value,lookup_array,match_type = 1)
  return lookup_value if lookup_value.is_a?(Symbol)
  return lookup_array if lookup_array.is_a?(Symbol)
  return match_type if match_type.is_a?(Symbol)
  
  lookup_value ||= 0
  lookup_array ||= [0]
  match_type ||= 0
  lookup_array = [lookup_array] unless lookup_array.is_a?(Array)
  lookup_array = lookup_array.flatten
  
  lookup_value = lookup_value.downcase if lookup_value.respond_to?(:downcase)
  case match_type      
  when 0, 0.0, false
    lookup_array.each_with_index do |item,index|
      item ||= 0
      item = item.downcase if item.respond_to?(:downcase)
      return index+1 if lookup_value == item
    end
    return :na
  when 1, 1.0, true
    lookup_array.each_with_index do |item, index|
      item ||= 0
      next if lookup_value.is_a?(String) && !item.is_a?(String)
      next if lookup_value.is_a?(Numeric) && !item.is_a?(Numeric)
      item = item.downcase if item.respond_to?(:downcase)
      if item > lookup_value
        return :na if index == 0
        return index
      end
    end
    return lookup_array.to_a.size
  when -1, -1.0
    lookup_array.each_with_index do |item, index|
      item ||= 0
      next if lookup_value.is_a?(String) && !item.is_a?(String)
      next if lookup_value.is_a?(Numeric) && !item.is_a?(Numeric)
      item = item.downcase if item.respond_to?(:downcase)
      if item < lookup_value
        return :na if index == 0
        return index
      end
    end
    return lookup_array.to_a.size - 1
  end
  return :na
end
excel_not(a) click to toggle source
# File src/excel/excel_functions/not.rb, line 3
def excel_not(a)
  return a if a.is_a?(Symbol)
  return :value if a.is_a?(String)
  return :value if a.is_a?(Array)
  a = false if a == nil
  a = false if a == 0
  a = true if a.is_a?(Numeric)
  !a
end
excel_or(*args) click to toggle source
# File src/excel/excel_functions/or.rb, line 3
 def excel_or(*args)
   # Flatten arrays
   args = args.flatten
   
   # If an argument is an error, return that
   error = args.find {|a| a.is_a?(Symbol)}
   return error if error
   
   # Replace 1 and 0 with true and false
   args.map! do |a|
     case a
     when 1; true
     when 0; false
     else; a
     end
   end
   
   # Remove anything not boolean
   args.delete_if { |a| !(a.is_a?(TrueClass) || a.is_a?(FalseClass)) }
   
   # Return an error if nothing less
   return :value if args.empty?
   
   # Now calculate and return
   args.any? {|a| a == true }
end
exp(a) click to toggle source
# File src/excel/excel_functions/exp.rb, line 3
def exp(a)
  a = number_argument(a)
  return a if a.is_a?(Symbol)

  Math.exp(a)
end
find(find_text,within_text,start_number = 1) click to toggle source
# File src/excel/excel_functions/find.rb, line 3
def find(find_text,within_text,start_number = 1)
  return find_text if find_text.is_a?(Symbol)
  return within_text if within_text.is_a?(Symbol)
  return start_number if start_number.is_a?(Symbol)

  # nils are treated as empty strings
  find_text ||= ""
  within_text ||= ""

  # Other things should be turned into a string
  find_text = find_text.to_s
  within_text = within_text.to_s
  
  # there are some cases where the start_number is remapped
  case start_number
  when nil; return :value
  when String; 
    begin
      start_number = Float(start_number)
    rescue ArgumentError
      return :value
    end
  when true; start_number = 1
  when false; return :value
  when Numeric; # ok
  else; return :value
  end
  
  # edge case
  return 1 if find_text == "" && within_text == "" && start_number == 1
  
  # length check
  return :value if start_number < 1
  return :value if start_number > within_text.length
    
  # Ok, lets go
  result = within_text.index(find_text,start_number - 1 )
  result ? result + 1 : :value
end
floor(number, multiple) click to toggle source
# File src/excel/excel_functions/floor.rb, line 3
def floor(number, multiple)
  return number if number.is_a?(Symbol)
  return multiple if multiple.is_a?(Symbol)
  number ||= 0
  return :value unless number.is_a?(Numeric)
  return :value unless multiple.is_a?(Numeric)
  return :div0 if multiple == 0
  return :num if multiple < 0
  number - (number % multiple)
end
forecast(required_x, known_y, known_x) click to toggle source
# File src/excel/excel_functions/forecast.rb, line 3
def forecast(required_x, known_y, known_x)
  required_x = number_argument(required_x)
  return required_x if required_x.is_a?(Symbol)
  return :na unless known_y.is_a?(Array)
  return :na unless known_x.is_a?(Array)
  known_y = known_y.flatten
  known_x = known_x.flatten
  known_y.each { |y| return y if y.is_a?(Symbol) }
  known_x.each { |x| return x if x.is_a?(Symbol) }
  return :na unless known_x.length == known_y.length
  return :na if known_y.empty?
  return :na if known_x.empty?

  0.upto(known_x.length-1).each do |i|
    known_x[i] = nil unless known_y[i].is_a?(Numeric)
    known_y[i] = nil unless known_x[i].is_a?(Numeric)
  end

  known_x.compact!
  known_y.compact!

  mean_y = known_y.inject(0.0) { |m,i| m + i.to_f }   / known_y.size.to_f
  mean_x = known_x.inject(0.0) { |m,i| m + i.to_f }   / known_x.size.to_f

  b_denominator = known_x.inject(0.0) { |s,x| x.is_a?(Numeric) ? (s + (x-mean_x)**2 ) : s  }
  return :div0 if b_denominator == 0
  b_numerator = 0.0
  known_x.each.with_index do |x,i|
    y = known_y[i]
    next unless x.is_a?(Numeric)
    next unless y.is_a?(Numeric)
    b_numerator = b_numerator + ((x-mean_x)*(y-mean_y))
  end

  b = b_numerator / b_denominator

  a = mean_y - (b * mean_x)

  return a + (b*required_x)
end
halfscurve(currentYear, startValue, endValue , duration, startYear = 2018) click to toggle source
# File src/excel/excel_functions/scurve.rb, line 28
def halfscurve(currentYear, startValue, endValue , duration, startYear = 2018)
  currentYear = number_argument(currentYear)
  startValue = number_argument(startValue)
  endValue = number_argument(endValue)
  duration = number_argument(duration)
  startYear = number_argument(startYear)

  return currentYear if currentYear.is_a?(Symbol)
  return startValue if startValue.is_a?(Symbol)
  return endValue if endValue.is_a?(Symbol)
  return duration if duration.is_a?(Symbol)
  return startYear if startYear.is_a?(Symbol)
  return startValue if currentYear < startYear 

  scurve(currentYear + duration, startValue, endValue, duration * 2, startYear) - ((endValue - startValue) / 2.0)
end
hlookup(lookup_value, lookup_table, row_number, match_type = true) click to toggle source
# File src/excel/excel_functions/hlookup.rb, line 3
def hlookup(lookup_value, lookup_table, row_number, match_type = true)    
  return lookup_value if lookup_value.is_a?(Symbol)
  return lookup_table if lookup_table.is_a?(Symbol)
  return row_number if row_number.is_a?(Symbol)
  return match_type if match_type.is_a?(Symbol)
  
  return :na if lookup_value == nil
  return :na if lookup_table == nil
  return :na if row_number == nil
  return :na if match_type == nil

  lookup_value = lookup_value.downcase if lookup_value.is_a?(String)
  
  last_good_match = 0

  return :value unless row_number > 0
  return :ref unless row_number <= lookup_table.size

  lookup_table.first.each_with_index do |possible_match, column_number|
    
    next if lookup_value.is_a?(String) && !possible_match.is_a?(String)
    next if lookup_value.is_a?(Numeric) && !possible_match.is_a?(Numeric)
    
    possible_match = possible_match.downcase if lookup_value.is_a?(String)

    if lookup_value == possible_match
      return lookup_table[row_number-1][column_number]
    elsif match_type == true
      if possible_match > lookup_value
        return :na if column_number == 0
        return lookup_table[row_number-1][last_good_match]
      else
        last_good_match = column_number
      end
    end      
  end

  # We don't have a match
  if match_type == true
    return lookup_table[row_number - 1][last_good_match]
  else
    return :na
  end
end
iferror(value,value_if_error) click to toggle source
# File src/excel/excel_functions/iferror.rb, line 3
def iferror(value,value_if_error)
  value_if_error ||= 0
  return value_if_error if value.is_a?(Symbol)
  return value_if_error if value.is_a?(Float) && value.nan?
  value
end
index(array, row_number, column_number = :not_specified) click to toggle source
# File src/excel/excel_functions/index.rb, line 3
def index(array, row_number, column_number = :not_specified)
  
  return array if array.is_a?(Symbol)
  return row_number if row_number.is_a?(Symbol)
  return column_number if column_number.is_a?(Symbol) && column_number != :not_specified

  array = [[array]] unless array.is_a?(Array)
      
  if column_number == :not_specified
    if array.length == 1 # It is a single row
      index_for_row_column(array,1,row_number)
    elsif array.first.length == 1 # it is a single column
      index_for_row_column(array,row_number,1)
    else
      return :ref
    end
  else
    if row_number == nil || row_number == 0
      index_for_whole_column(array,column_number)    
    elsif column_number == nil || column_number == 0
      index_for_whole_row(array,row_number)
    else 
      index_for_row_column(array,row_number,column_number)
    end
  end
end
index_for_row_column(array,row_number,column_number) click to toggle source
# File src/excel/excel_functions/index.rb, line 30
def index_for_row_column(array,row_number,column_number)
  return :ref if row_number < 1 || row_number > array.length
  row = array[row_number-1]
  return :ref if column_number < 1 || column_number > row.length
  row[column_number-1] || 0
end
index_for_whole_column(array,column_number) click to toggle source
# File src/excel/excel_functions/index.rb, line 44
def index_for_whole_column(array,column_number)
  return :ref if column_number < 1
  return :ref if column_number > array[0].length
  return index_for_row_column(array, 1, column_number) if array.length == 1
  array.map { |row| [row[column_number-1]]}
end
index_for_whole_row(array,row_number) click to toggle source
# File src/excel/excel_functions/index.rb, line 37
def index_for_whole_row(array,row_number)
  return :ref if row_number < 1
  return :ref if row_number > array.length
  return index_for_row_column(array, row_number, 1) if array.first.length == 1
  [array[row_number-1]]
end
int(number) click to toggle source
# File src/excel/excel_functions/int.rb, line 3
def int(number)
  number = number_argument(number)
  return number if number.is_a?(Symbol)
  number.floor.to_f
end
isblank(a) click to toggle source
# File src/excel/excel_functions/isblank.rb, line 3
def isblank(a)
  return true if a == nil
  false
end
iserr(a) click to toggle source
# File src/excel/excel_functions/iserr.rb, line 3
def iserr(a)
  return false if a == :na
  return true if a.is_a?(Symbol)
  false
end
iserror(a) click to toggle source
# File src/excel/excel_functions/iserror.rb, line 3
def iserror(a)
  return true if a.is_a?(Symbol)
  false
end
isnumber(a) click to toggle source
# File src/excel/excel_functions/isnumber.rb, line 3
def isnumber(a)
  return true if a.is_a?(Numeric)
  false
end
large(range, k) click to toggle source
# File src/excel/excel_functions/large.rb, line 3
def large(range, k)
  range = [range] unless range.is_a?(Array)
  range = range.flatten
  error = range.find {|a| a.is_a?(Symbol)}
  error ||= k if k.is_a?(Symbol)
  return error if error
  range.delete_if { |v| !v.is_a?(Numeric) }
  return :value unless k.is_a?(Numeric)
  return :num unless k>0 && k<=range.size
  range.sort!
  range.reverse!
  range[k-1]
end
lcurve(currentYear, startValue, endValue , duration, startYear = 2018) click to toggle source
# File src/excel/excel_functions/scurve.rb, line 45
def lcurve(currentYear, startValue, endValue , duration, startYear = 2018)
  currentYear = number_argument(currentYear)
  startValue = number_argument(startValue)
  endValue = number_argument(endValue)
  duration = number_argument(duration)
  startYear = number_argument(startYear)

  return currentYear if currentYear.is_a?(Symbol)
  return startValue if startValue.is_a?(Symbol)
  return endValue if endValue.is_a?(Symbol)
  return duration if duration.is_a?(Symbol)
  return startYear if startYear.is_a?(Symbol)

  return startValue if currentYear < startYear 
  return endValue if currentYear > (startYear + duration)
  return startValue if currentYear < startYear 
  startValue + (endValue - startValue) / duration * (currentYear - startYear)
end
left(string,characters = 1) click to toggle source
# File src/excel/excel_functions/left.rb, line 3
def left(string,characters = 1)
  return string if string.is_a?(Symbol)
  return characters if characters.is_a?(Symbol)
  return nil if string == nil || characters == nil
  return :value if characters < 0
  string = "TRUE" if string == true
  string = "FALSE" if string == false
  string.to_s.slice(0,characters)
end
len(a) click to toggle source
# File src/excel/excel_functions/len.rb, line 3
def len(a)
  return a if a.is_a?(Symbol)
  a.to_s.length
end
less_than?(a,b) click to toggle source
# File src/excel/excel_functions/less_than.rb, line 5
def less_than?(a,b)
  # return apply_to_range(a,b) { |a,b| less_than?(a,b) } if a.is_a?(Array) || b.is_a?(Array)
  
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)

  a = 0 if a == nil
  b = 0 if b == nil
  
  case a
  when String
    case b
    when String
      a.downcase < b.downcase
    when Numeric
      false
    when TrueClass, FalseClass
      true
    end
  when TrueClass
    false
  when FalseClass
    b.is_a?(TrueClass)
  when Numeric
    case b
    when Numeric
      a < b
    when String
      true
    when TrueClass, FalseClass
      true
    end
  end
end
less_than_or_equal?(a,b) click to toggle source
# File src/excel/excel_functions/less_than_or_equal.rb, line 5
def less_than_or_equal?(a,b)
  opposite = more_than?(a,b)
  return opposite if opposite.is_a?(Symbol)
  !opposite
end
ln(a) click to toggle source
# File src/excel/excel_functions/ln.rb, line 3
def ln(a)
  a = number_argument(a)
  
  return a if a.is_a?(Symbol)

  return :num if a <= 0

  Math.log(a)

end
log(a, b = 10) click to toggle source
# File src/excel/excel_functions/log.rb, line 3
def log(a, b = 10)
  a = number_argument(a)
  b = number_argument(b)
  
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)

  return :num if a <= 0
  return :num if b <= 0

  Math.log(a) / Math.log(b)

end
lower(string) click to toggle source
# File src/excel/excel_functions/lower.rb, line 3
def lower(string)
  return string if string.is_a?(Symbol)
  case  string
  when nil; ""
  when String; string.downcase
  when Numeric
    if string.round == string
      string.to_i.to_s
    else
      string.to_s
    end
  when true; "true"
  when false; "false"
  else
    string.to_s
  end
end
max(*args) click to toggle source
# File src/excel/excel_functions/max.rb, line 3
def max(*args)
  args = args.flatten
  error = args.find {|a| a.is_a?(Symbol)}
  return error if error
  args.delete_if { |a| !a.is_a?(Numeric) }
  return 0 if args.empty?
  args.max
end
mid(text, start_num, num_chars) click to toggle source
# File src/excel/excel_functions/mid.rb, line 3
def mid(text, start_num, num_chars)
  start_num = number_argument(start_num)
  num_chars = number_argument(num_chars)

  return text if text.is_a?(Symbol)
  return start_num if start_num.is_a?(Symbol)
  return num_chars if num_chars.is_a?(Symbol)

  text = text.to_s

  return :value if start_num < 1
  return :value if num_chars < 0

  return "" if start_num > text.length
  text[start_num - 1, num_chars]
end
min(*args) click to toggle source
# File src/excel/excel_functions/min.rb, line 3
def min(*args)
  args = args.flatten
  error = args.find {|a| a.is_a?(Symbol)}
  return error if error
  args.delete_if { |a| !a.is_a?(Numeric) }
  return 0 if args.empty?
  args.min
end
mmult(array_a, array_b) click to toggle source
# File src/excel/excel_functions/mmult.rb, line 3
def mmult(array_a, array_b)
  return array_a if array_a.is_a?(Symbol)
  return array_b if array_b.is_a?(Symbol)
  return :value unless array_a.is_a?(Array)
  return :value unless array_b.is_a?(Array)

  columns = array_a.first.length
  rows = array_b.length
  error = Array.new([rows, columns].max) { Array.new([rows, columns].max, :value) }
  return error unless columns == rows
  return error unless array_a.all? { |a| a.all? { |b| b.is_a?(Numeric) }} 
  return error unless array_b.all? { |a| a.all? { |b| b.is_a?(Numeric) }}
  result = Array.new(array_a.length) { Array.new(array_b.first.length) }
  indices = (0...rows).to_a
  result.map.with_index do |row, i|
    row.map.with_index do |cell, j|
      indices.inject(0) do |sum, n|
        sum = sum + (array_a[i][n] * array_b[n][j])
      end
    end
  end
end
mod(a,b) click to toggle source
# File src/excel/excel_functions/mod.rb, line 3
def mod(a,b)
  a = number_argument(a)
  b = number_argument(b)
  
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  
  return :div0 if b == 0
  
  a % b
end
more_than?(a,b) click to toggle source
# File src/excel/excel_functions/more_than.rb, line 5
def more_than?(a,b)
  # return apply_to_range(a,b) { |a,b| more_than?(a,b) } if a.is_a?(Array) || b.is_a?(Array)
  
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  
  a = 0 if a == nil
  b = 0 if b == nil

  case a
  when String
    case b
    when String
      a.downcase > b.downcase
    when Numeric
      true
    when TrueClass, FalseClass
      false
    end
  when TrueClass
    !b.is_a?(TrueClass)
  when FalseClass
    case b
    when TrueClass, FalseClass
      false
    else
      true
    end
  when Numeric
    case b
    when Numeric
      a > b
    else 
      false
    end
  end
end
more_than_or_equal?(a,b) click to toggle source
# File src/excel/excel_functions/more_than_or_equal.rb, line 5
def more_than_or_equal?(a,b)
  opposite = less_than?(a,b)
  return opposite if opposite.is_a?(Symbol)
  !opposite
end
mround(value, multiple) click to toggle source
# File src/excel/excel_functions/mround.rb, line 3
def mround(value, multiple)
  value = number_argument(value)
  multiple = number_argument(multiple)
  return value if value.is_a?(Symbol)
  return multiple if multiple.is_a?(Symbol)

  # Must both have the same sign
  return :num unless (value < 0) == (multiple < 0)

  # Zeros just return zero
  return 0 if value == 0
  return 0 if multiple == 0

  (value.to_f / multiple.to_f).round * multiple.to_f
end
multiply(a,b) click to toggle source
# File src/excel/excel_functions/multiply.rb, line 6
def multiply(a,b)
  a = number_argument(a)
  b = number_argument(b)
  
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  
  a * b
end
na() click to toggle source
# File src/excel/excel_functions/na.rb, line 3
def na()
  return :na
end
negative(a) click to toggle source
# File src/excel/excel_functions/negative.rb, line 3
def negative(a)
  return a.map { |c| negative(c) } if a.is_a?(Array)

  a = number_argument(a)

  return a if a.is_a?(Symbol)
  
  -a

end
not_equal?(a,b) click to toggle source
# File src/excel/excel_functions/not_equal.rb, line 5
def not_equal?(a,b)
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  return a.downcase != b.downcase if a.is_a?(String) && b.is_a?(String)
  a != b
end
npv(rate, *values) click to toggle source
# File src/excel/excel_functions/npv.rb, line 3
def npv(rate, *values)
  # Turn the arguments into numbers
  rate = number_argument(rate)
  values = values.flatten.map { |v| number_argument(v) }

  # Check for errors
  return rate if rate.is_a?(Symbol)
  return :div0 if rate == -1
  values.each { |v| return v if v.is_a?(Symbol) }

  npv = 0

  values.each.with_index { |v, i| npv = npv + (v/((1+rate)**(i+1))) }

  npv
end
number_argument(a) click to toggle source

This is a support function for mapping arguments that are numbers to numeric values deals with the fact that in Excel “2.14” == 2.14, TRUE == 1, FALSE == 0 and nil == 0

# File src/excel/excel_functions/number_argument.rb, line 5
def number_argument(a)
  case a
  when Symbol
    return a
  when String
    begin
      return Float(a)
    rescue ArgumentError
      return :value
    end
  when nil
    return 0
  when true
    return 1
  when false
    return 0
  when Numeric
    return a
  when Array
    return number_argument(a[0][0])
  else
    return :value
  end
end
number_or_zero(a) click to toggle source
# File src/excel/excel_functions/number_or_zero.rb, line 3
def number_or_zero(a)
  return a if a.is_a?(Symbol)
  return a if a.is_a?(Numeric)
  0
end
pi() click to toggle source
# File src/excel/excel_functions/pi.rb, line 3
def pi
  Math::PI
end
pmt(rate,number_of_periods,present_value, final_value = 0, type = 0) click to toggle source
# File src/excel/excel_functions/pmt.rb, line 3
def pmt(rate,number_of_periods,present_value, final_value = 0, type = 0)
  
  rate = number_argument(rate)
  number_of_periods = number_argument(number_of_periods)
  present_value = number_argument(present_value)
  final_value = number_argument(final_value)
  type = number_argument(type)

  return rate if rate.is_a?(Symbol)
  return number_of_periods if number_of_periods.is_a?(Symbol)
  return present_value if present_value.is_a?(Symbol)
  return final_value if final_value.is_a?(Symbol)
  return type if type.is_a?(Symbol)
  
  return :num if number_of_periods == 0

  raise NotSupportedException.new("pmt() function non zero final value not implemented") unless final_value == 0
  raise NotSupportedException.new("pmt() function non zero type not implemented") unless type == 0
  
  return -(present_value / number_of_periods) if rate == 0
  -present_value*(rate*((1+rate)**number_of_periods))/(((1+rate)**number_of_periods)-1)
end
power(a,b) click to toggle source
# File src/excel/excel_functions/power.rb, line 6
def power(a,b)
  a = number_argument(a)
  b = number_argument(b)
  
  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)

  return 1 if b ==0 # Special case so can do the following negative number check
  return :num if a < 0 && b < 1
  
  a**b
end
product(*args) click to toggle source
# File src/excel/excel_functions/product.rb, line 3
def product(*args)
  args = args.flatten
  args.find {|a| a.is_a?(Symbol)} || args.delete_if { |a| !a.is_a?(Numeric) }.inject(0) { |m,i| m + i }
end
pv(rate, nper, pmt, fv = nil, type = nil) click to toggle source
# File src/excel/excel_functions/pv.rb, line 3
def pv(rate, nper, pmt, fv = nil, type = nil)
  # Turn the remainder into numbers
  rate = number_argument(rate)
  nper = number_argument(nper)
  pmt = number_argument(pmt)
  fv = number_argument(fv)
  type = number_argument(type)

  # Check for errors
  return rate if rate.is_a?(Symbol)
  return nper if nper.is_a?(Symbol)
  return pmt if pmt.is_a?(Symbol)
  return fv if fv.is_a?(Symbol)

  return :value unless (type == 1 || type == 0)
  return :value unless rate >= 0

  # Sum the payments
  if rate > 0
    present_value = -pmt * ((1 - ((1 + rate)**-nper))/rate)
  else
    present_value = -pmt * nper
  end

  # Adjust for the type, which governs whether payments at the beginning or end of the period
  present_value = present_value * ( 1 + rate) if type == 1

  # Add on the final value
  present_value += -fv / ((1 + rate)**(nper))

  # Return the answer
  present_value
end
rank(number, list, order = 0) click to toggle source
# File src/excel/excel_functions/rank.rb, line 3
def rank(number, list, order = 0)
  number = number_argument(number)
  order = number_argument(order)
  list = [[list]] unless list.is_a?(Array)

  return number if number.is_a?(Symbol)
  return order if order.is_a?(Symbol)
  return list if list.is_a?(Symbol)
  
  ranked = 1
  found = false

  list.flatten.each do |cell|
    return cell if cell.is_a?(Symbol)
    next unless cell.is_a?(Numeric)
    found = true if cell == number
    if order == 0
      ranked += 1 if cell > number
    else
      ranked +=1 if cell < number
    end
  end
  return :na unless found
  return ranked
end
rate(periods, payment, presentValue, finalValue) click to toggle source
# File src/excel/excel_functions/rate.rb, line 3
def rate(periods, payment, presentValue, finalValue)
  raise NotSupportedException.new("rate() function has not been implemented fully. Edit src/excel/excel_functions/rate.rb") unless (payment || 0) == 0
  return periods if periods.is_a?(Symbol)
  return presentValue if presentValue.is_a?(Symbol)
  return finalValue if finalValue.is_a?(Symbol)

  return :num unless periods.is_a?(Numeric)
  return :num unless presentValue.is_a?(Numeric)
  return :num unless finalValue.is_a?(Numeric)

  ((finalValue.to_f/(-presentValue.to_f)) ** (1.0/periods))-1.0
end
replace(string, start, length, replacement) click to toggle source
# File src/excel/excel_functions/replace.rb, line 3
def replace(string, start, length, replacement)
  return string if string.is_a?(Symbol)
  return start if start.is_a?(Symbol)
  return length if length.is_a?(Symbol)
  return replacement if replacement.is_a?(Symbol)
  string = string.to_s
  return string + replacement if start >= string.length
  string_join(left(string,start-1),replacement,right(string,string.length - (start - 1 + length)))
end
reset() click to toggle source

This is a support function for reseting a spreadsheet's instance variables back to nil, allowing the results to be recalculated

# File src/excel/excel_functions/reset.rb, line 5
def reset()
  # Set all the instance variables to nil
  instance_variables.each do |iv|
    instance_variable_set(iv,nil)
  end
  # Reset the settable variables to their defaults
  initialize
end
right(string,characters = 1) click to toggle source
# File src/excel/excel_functions/right.rb, line 3
def right(string,characters = 1)
  return string if string.is_a?(Symbol)
  return characters if characters.is_a?(Symbol)
  return nil if string == nil || characters == nil
  return :value if characters < 0
  string = "TRUE" if string == true
  string = "FALSE" if string == false
  string = string.to_s
  characters = string.length if characters > string.length
  string.slice(string.length-characters,characters) || ""
end
round(number,decimal_places) click to toggle source
# File src/excel/excel_functions/round.rb, line 3
def round(number,decimal_places)
  number = number_argument(number)
  decimal_places = number_argument(decimal_places)
  
  return number if number.is_a?(Symbol)
  return decimal_places if decimal_places.is_a?(Symbol)
  
  number.round(decimal_places)
end
rounddown(number,decimal_places) click to toggle source
# File src/excel/excel_functions/rounddown.rb, line 3
def rounddown(number,decimal_places)
  number = number_argument(number)
  decimal_places = number_argument(decimal_places)
  
  return number if number.is_a?(Symbol)
  return decimal_places if decimal_places.is_a?(Symbol)
  
  (number * 10**decimal_places).truncate.to_f / 10**decimal_places
end
roundup(number,decimal_places) click to toggle source
# File src/excel/excel_functions/roundup.rb, line 3
def roundup(number,decimal_places)
  number = number_argument(number)
  decimal_places = number_argument(decimal_places)
  
  return number if number.is_a?(Symbol)
  return decimal_places if decimal_places.is_a?(Symbol)
  
  if number < 0
    (number * 10**decimal_places).floor.to_f / 10**decimal_places
  else
    (number * 10**decimal_places).ceil.to_f / 10**decimal_places
  end
end
scurve(currentYear, startValue, endValue, duration, startYear = 2018.0) click to toggle source
# File src/excel/excel_functions/scurve.rb, line 3
def scurve(currentYear, startValue, endValue, duration, startYear = 2018.0)
  currentYear = number_argument(currentYear)
  startValue = number_argument(startValue)
  endValue = number_argument(endValue)
  duration = number_argument(duration)
  startYear = number_argument(startYear)

  return currentYear if currentYear.is_a?(Symbol)
  return startValue if startValue.is_a?(Symbol)
  return endValue if endValue.is_a?(Symbol)
  return duration if duration.is_a?(Symbol)
  return startYear if startYear.is_a?(Symbol)
  
  return startValue if currentYear < startYear 

  x = (currentYear - startYear) / duration.to_f
  x0 = 0.0
  a = endValue - startValue
  sc = 0.999
  eps = 1.0 - sc
  mu = 0.5
  beta = (mu - 1.0) / Math.log(1.0 / sc - 1)
  scurve = a * (((Math.exp(-(x - mu) / beta) + 1) ** -1) - ((Math.exp(-(x0 - mu) / beta) + 1) ** -1)) + startValue
end
sqrt(a) click to toggle source
# File src/excel/excel_functions/sqrt.rb, line 3
def sqrt(a)
  return a if a.is_a?(Symbol)
  a ||= 0
  return :value unless a.is_a?(Numeric)
  return :num if a < 0
  Math.sqrt(a)
end
string_argument(a) click to toggle source

This is a support function for mapping arguments that are not strings to their Excel string equivalent. e.g., true = TRUE, :div0 = “DIV/0!”

# File src/excel/excel_functions/string_argument.rb, line 5
def string_argument(a)
  case a
  when Symbol
    return {
      :name => "#NAME?",
      :value => "#VALUE!",
      :div0 => "#DIV/0!",
      :ref => "#REF!",
      :na => "#N/A",
      :num => "#NUM!",
    }[a] || :value
  when String
    return a
  when nil
    return "" 
  when true
    return "TRUE"
  when false
    return "FALSE" 
  when Numeric
    if a.round == a
      return a.to_i.to_s
    else
      return a.to_s
    end
  when Array
    return string_argument(a[0][0])
  else
    return :value
  end
end
string_join(*strings) click to toggle source
# File src/excel/excel_functions/string_join.rb, line 3
def string_join(*strings)
  strings.find {|s| s.is_a?(Symbol)} || strings.map do |s| 
    case s
    when nil; "0"
    when Numeric
      if s.round == s
        s.to_i.to_s
      else
        s.to_s
      end
    else
      s.to_s
    end
  end.join('')
end
substitute(text, old_text, new_text, instance_num = :any) click to toggle source
# File src/excel/excel_functions/substitute.rb, line 3
def substitute(text, old_text, new_text, instance_num = :any)
  # Check for errors
  return text if text.is_a?(Symbol)
  return old_text if old_text.is_a?(Symbol)
  return new_text if new_text.is_a?(Symbol)
  # Nils get turned into blanks
  text ||= ""
  new_text ||= ""
  old_text ||= ""
  return text if old_text == ""
  # Booleans get made into text, but need to be TRUE not true
  text = text.to_s.upcase if text.is_a?(TrueClass) || text.is_a?(FalseClass)
  old_text = old_text.to_s.upcase if old_text.is_a?(TrueClass) || old_text.is_a?(FalseClass)
  new_text = new_text.to_s.upcase if new_text.is_a?(TrueClass) || new_text.is_a?(FalseClass)
  # Other items get turned into text
  text = text.to_s
  old_text = old_text.to_s
  new_text = new_text.to_s
  # Now split based on whether instance_num is passed
  if instance_num == :any
    # The easy case
    text.gsub(old_text, new_text)
  else
    # The harder case
    return instance_num if instance_num.is_a?(Symbol)
    return :value unless instance_num.to_i > 0
    instances_found = 0
    text.gsub(old_text) do |match|
      instances_found += 1
      if instances_found == instance_num
        new_text
      else
        old_text
      end
    end
  end
end
subtotal(type,*args) click to toggle source
# File src/excel/excel_functions/subtotal.rb, line 3
def subtotal(type,*args)
  case number_argument(type)
  when Symbol; type
  when 1.0, 101.0; average(*args)
  when 2.0, 102.0; count(*args)
  when 3.0, 103.0; counta(*args)
  when 9.0, 109.0; sum(*args)
  else :value
  end
end
subtract(a,b) click to toggle source
# File src/excel/excel_functions/subtract.rb, line 6
def subtract(a,b)
  # return apply_to_range(a,b) { |a,b| subtract(a,b) } if a.is_a?(Array) || b.is_a?(Array)

  a = number_argument(a)
  b = number_argument(b)

  return a if a.is_a?(Symbol)
  return b if b.is_a?(Symbol)
  
  a - b
end
sum(*args) click to toggle source
# File src/excel/excel_functions/sum.rb, line 3
def sum(*args)
  args = args.flatten
  args.find {|a| a.is_a?(Symbol)} || args.delete_if { |a| !a.is_a?(Numeric) }.inject(0) { |m,i| m + i }
end
sumif(check_range,criteria,sum_range = check_range) click to toggle source
# File src/excel/excel_functions/sumif.rb, line 3
def sumif(check_range,criteria,sum_range = check_range)
  sumifs(sum_range,check_range,criteria)
end
sumifs(range,*criteria) click to toggle source
# File src/excel/excel_functions/sumifs.rb, line 96
def sumifs(range,*criteria)
  filtered = _filtered_range(range,*criteria)
  sum(*filtered)
end
sumproduct(*args) click to toggle source
# File src/excel/excel_functions/sumproduct.rb, line 3
def sumproduct(*args)
  error = args.find { |a| a.is_a?(Symbol) }
  return error if error
  return :value if args.any? { |a| a == nil }
  args = args.map { |a| a.is_a?(Array) ? a : [[a]] }
  first = args.shift
  accumulator = 0
  first.each_with_index do |row,row_number|
    row.each_with_index do |cell,column_number|
      next unless cell.is_a?(Numeric)
      product = cell
      args.each do |area|
        return :value unless area.length > row_number
        r = area[row_number]
        return :value unless r.length > column_number
        c = r[column_number]
        if c.is_a?(Numeric)
          product = product * c
        else
          product = product * 0
          break
        end
      end
      accumulator += product
    end
  end
  accumulator
end
text(number, format) click to toggle source
# File src/excel/excel_functions/text.rb, line 3
def text(number, format)
  number ||= 0
  return "" unless format
  format = "0" if format == 0.0

  if number.is_a?(String)
    begin
      number = Float(number)
    rescue ArgumentError => e
      # Ignore
    end
  end
  return number unless number.is_a?(Numeric)
  return format if format.is_a?(Symbol)

  case format
  when /^(0([.,]0*)?)%/
    text(number*100, $1)+"%"
  when /^(0+)$/
    sprintf("%0#{$1.length}.0f", number)
  when /^#[,.]#*(0[.,]0+)?(0*)$/
    formated_with_decimals = text(number, $1 || "0")
    parts = formated_with_decimals.split('.')
    if $2 && parts[0].length < $2.length
      parts[0] = ("0"*($2.length - parts[0].length))+parts[0]
    end
    parts[0].gsub!(/(\d)(?=(\d\d\d)+(?!\d))/, "\\1,")
    parts.join('.')
  when /0[.,](0+)/
    sprintf("%.#{$1.length}f", number)
  else
    raise ExcelToCodeException.new("in TEXT function format #{format} not yet supported by excel_to_code")
  end
end
trim(text) click to toggle source
# File src/excel/excel_functions/trim.rb, line 3
def trim(text)
  return text unless text.is_a?(String)
  text.strip.gsub(/ +/,' ')
end
value(a) click to toggle source
# File src/excel/excel_functions/value.rb, line 3
def value(a)
   number_argument(a)
end
vlookup(lookup_value,lookup_table,column_number, match_type = true) click to toggle source
# File src/excel/excel_functions/vlookup.rb, line 3
def vlookup(lookup_value,lookup_table,column_number, match_type = true)    
  return lookup_value if lookup_value.is_a?(Symbol)
  return lookup_table if lookup_table.is_a?(Symbol)
  return column_number if column_number.is_a?(Symbol)
  return match_type if match_type.is_a?(Symbol)
  
  return :na if lookup_value == nil
  return :na if lookup_table == nil
  return :na if column_number == nil
  return :na if match_type == nil

  lookup_value = lookup_value.downcase if lookup_value.is_a?(String)
  
  last_good_match = 0
  
  lookup_table.each_with_index do |row,index|
    possible_match = row.first
    
    next if lookup_value.is_a?(String) && !possible_match.is_a?(String)
    next if lookup_value.is_a?(Numeric) && !possible_match.is_a?(Numeric)
    
    possible_match = possible_match.downcase if lookup_value.is_a?(String)

    if lookup_value == possible_match
      return :value unless column_number <= row.length
      return row[column_number-1]
    elsif match_type == true
      if possible_match > lookup_value
        return :na if index == 0
        previous_row = lookup_table[last_good_match]
        return :value unless column_number <= previous_row.length
        return previous_row[column_number-1]
      else
        last_good_match = index
      end
    end      
  end

  # We don't have a match
  if match_type == true
    return lookup_table[last_good_match][column_number-1]
  else
    return :na
  end
end