• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

POI Excel Large Function shows #VALUE!

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When I try to use the "Large" excel function all I see in the resulting cell is #VALUE!. The formula looks correct in excel and if I click in the formula editor and just hit return it replaces the #VALUE! with the correct result.
Here is the excel formula
=SUM(LARGE(C3:C90,1),LARGE(C3:C90,2),LARGE(C3:C90,3))

and here is the code snippet that creates the cell in POI/Java
c.setCellType(HSSFCell.CELL_TYPE_FORMULA);

String cells = colLetter + "3:" + colLetter + (numRows);
String cellFormula = "Sum(Large(" + cells + ",1), Large(" + cells + ",2), Large(" + cells + ",3))";
c.setCellFormula(cellFormula);

Is Large not supported by POI?
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's not that LARGE isn't supported, it's that array formulas (which is what LARGE takes as its first parameters) aren't supported.

I'm not sure if other Excel libraries like jExcelApi support this.
[ February 21, 2007: Message edited by: Ulf Dittmer ]
 
Kimberly Greuling
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't know much about the Excel file format, but does Sum(A1:A56) use an array function too, because this works just fine?
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No, SUM uses a range of numbers. You can check which is which by going to the Insert -> Functions menu, and look up the function you're curious about. The example at the bottom will reveal the type of parameter expected.
 
It is difficult to free fools from the chains they revere - Voltaire. tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic