This project has moved. For the latest updates, please go here.

How to do a CSE formula?

Feb 4, 2014 at 7:42 PM
Array Formula, CSE Formula, CTRL+SHIFT+ENTER formula - Whatever you call it here's what I'm trying to do:
  • Calculate a row's minimum value excluding cells that have a 0 value
  • Calculate a row's maximum value excluding cells that have a 0 value
  • Calculate a row's standard deviation excluding cells that have a 0 value
By typing the following formulas into an excel spreadsheet and hitting CTRL+SHIFT+ENTER at the end of the formula gives me what I want:
  • Minimum: =MIN(IF(B2:SS2<>0,B2:SS2))
  • Maximum: =MAX(IF(B2:SS2<>0,B2:SS2))
  • Standard Deviation: =STDEV(IF(B2:SS2<>0,B2:SS2))
But if I just hit ENTER it gives me an error message (A value used in the formula is of the wrong data type.)

When I try to assign these formulas while building a spreadsheet via EPPLUS
wkst.Cells(row, column).Formula = "min(if(" & range & "<>0," & range & "))"
wkst.Cells(row, column).Formula = "max(if(" & range & "<>0," & range & "))"
wkst.Cells(row, column).Formula = "stdev(if(" & range & "<>0," & range & "))"
these cells give the same error when I open the file, but if it edit the cell and hit CTRL+SHIFT+ENTER it calculates the values just fine.

Is there a way to have EPPLUS properly identify these formulas as Array Formulas?