excel_functions_spec.rb in rubyfromexcel-0.0.13

- old
+ new

@@ -86,12 +86,33 @@
 describe "sumif" do
   it "should only sum values in the area that meet the criteria" do
     FunctionTest.sumif(FunctionTest.a('a1','a3'),10.0).should == 10.0
     FunctionTest.sumif(FunctionTest.a('b1','b3'),'Bear',FunctionTest.a('a1','a2')).should == 100.0
   end
+  
+  it "should understand >0 type criteria" do
+    FunctionTest.sumif(FunctionTest.a('a1','a3'),">0").should == 110.0
+    FunctionTest.sumif(FunctionTest.a('a1','a3'),">10").should == 100.0
+    FunctionTest.sumif(FunctionTest.a('a1','a3'),"<100").should == 10.0
+  end
+  
 end
 
+describe "countif" do
+  it "should only count values in the area that meet the criteria" do
+    FunctionTest.countif(FunctionTest.a('a1','a3'),10.0).should == 1.0
+    FunctionTest.sumif(FunctionTest.a('b1','b3'),'Bear',FunctionTest.a('a1','a2')).should == 100.0
+  end
+  
+  it "should understand >0 type criteria" do
+    FunctionTest.countif(FunctionTest.a('a1','a3'),">0").should == 2.0
+    FunctionTest.countif(FunctionTest.a('a1','a3'),">10").should == 1.0
+    FunctionTest.countif(FunctionTest.a('a1','a3'),"<100").should == 1.0
+  end
+  
+end
+
 describe "sumifs" do
   it "should only sum values that meet all of the criteria" do
     FunctionTest.sumifs(FunctionTest.a('a1','a3'),FunctionTest.a('a1','a3'),10.0,FunctionTest.a('b1','b3'),'Bear').should == 0.0
     FunctionTest.sumifs(FunctionTest.a('a1','a3'),FunctionTest.a('a1','a3'),10.0,FunctionTest.a('b1','b3'),'Pear').should == 10.0
   end
@@ -103,10 +124,15 @@
 
 describe "sumproduct" do
   it "should multiply together and then sum the elements in row or column areas given as arguments" do
     FunctionTest.sumproduct(FunctionTest.a('a1','a3'),FunctionTest.a('zx10','zz10')).should == 320.0
   end
+
+  it "should return :value when miss-matched array sizes" do
+    FunctionTest.sumproduct(FunctionTest.a('a1','a4'),FunctionTest.a('zx10','zz10')).should == :value
+  end
+  
 end
 
 describe "count" do
   it "should count the number of numeric values in an area" do
     FunctionTest.count(1,"two",FunctionTest.a('a1','a3')).should == 3    
@@ -148,11 +174,12 @@
     FunctionTest.match('bEAr',FunctionTest.a('b1','b3'),0.0).should == 2
     FunctionTest.match(1000.0,FunctionTest.a('a1','a2'),1.0).should == 2
     FunctionTest.match(1.0,FunctionTest.a('a1','a2'),1.0).should == :na
     FunctionTest.match('Care',FunctionTest.a('b1','b3'),-1.0).should == 1  
     FunctionTest.match('Zebra',FunctionTest.a('b1','b3'),-1.0).should == :na
-    FunctionTest.match('a',FunctionTest.a('b1','b3'),-1.0).should == 2    
+    FunctionTest.match('a',FunctionTest.a('b1','b3'),-1.0).should == 2
+    # FunctionTest.match("v.02",["p.01","V.01","v.05"],"false").should == 2
   end
 end
 
 describe "index" do
   it "should return the value at the row and column number given" do
@@ -278,10 +305,40 @@
     FunctionTest.pmt(0.1,10,100).should be_within(0.01).of(-16.27)
     FunctionTest.pmt(0.0123,99.1,123.32).should be_within(0.01).of(-2.159)
   end
 end
 
+describe "npv" do
+  it "should calculate the discounted value of future cash flows" do
+    FunctionTest.npv(0.1,-10000,3000,4200,6800).should be_within(0.01).of(1188.44)
+    FunctionTest.npv(0.08,8000,9200,10000,12000,14500).should be_within(0.01).of(1922.06+40000)
+    FunctionTest.npv(0.08,8000,9200,10000,12000,14500,-9000).should be_within(0.01).of(-3749.47+40000)
+    FunctionTest.npv(0.1,FunctionTest.a('a1','a2'),20).should  be_within(0.01).of(106.76)
+  end
+end
+
+describe "excel comparisons" do
+  
+  it "should carry out comparisons in the usual way" do
+    FunctionTest.excel_comparison(10,"==",5).should == false
+    FunctionTest.excel_comparison(10,"<=",5).should == false
+    FunctionTest.excel_comparison(10,">=",5).should == true
+    FunctionTest.excel_comparison(10,"<",5).should == false
+    FunctionTest.excel_comparison(10,">",5).should == true
+    FunctionTest.excel_comparison(10,"==",10).should == true
+    FunctionTest.excel_comparison(10,"!=",10).should == false
+    FunctionTest.excel_comparison(10,"<=",10).should == true
+    FunctionTest.excel_comparison(10,">=",10).should == true
+    FunctionTest.excel_comparison(10,"<",10).should == false
+    FunctionTest.excel_comparison(10,">",10).should == false 
+  end
+
+  it "should test for equality, ignoring string case" do        
+    FunctionTest.excel_comparison("A","==","a").should == true
+  end
+end
+
 describe "ability to respond to empty cell references" do
   it "should return 0 if a reference is made to an empty cell" do
     FunctionTest.a23.should == 0.0
   end
   
@@ -311,9 +368,12 @@
     @workbook_tables = {"FirstTable"=>'Table.new(sheet1,"FirstTable","A1:C3",["ColA", "ColB", "ColC"],1)'}
   end
   def sheet1
     self
   end
+  
+  def name; "sheet1"; end
+    
   def a1; "Cell A1"; end
   def a2; "Middle A2"; end
   def a3; "Total A3"; end
   def to_s; 'sheet1'; end
 end
\ No newline at end of file