Farpoint Spread is Evaluating a Blank Cell as the Numeral "1"....

Posted by: cruisefx on 8 September 2017, 2:19 pm EST

  • Posted 8 September 2017, 2:19 pm EST

    I have a spreadsheet that I thought I could quickly integrate into Farpoint Spread.  It is a simple calculator of sorts with no macros.

     There are three cells like so:

     [Number of Rolls] [Rolls Total] [Number of Coins in a Roll]

     The input cell is the first one, the number of rolls.  I have defined Number of Rolls as a Number, Rolls Total as a Currency, and Number of Coins in a Roll as a number.

    The Rolls total formula looks like this: =PRODUCT([Number of Rolls Cell], [Number of Coins in a Roll Cell], .25)

    When entering the number of rolls in this cell, it works if a correct, whole number is inputted.  However, if one backspaces all of the input characters and presses enter, the Farpoint Spread evaluates this cell as a '1', not a '0'., and so the PRODUCT formula calculates the [Number of Rolls Cell] just like there were a '1' in there.

     Also, I use the SetNull Property as 0.  However, this evaulates as a string, and whenever the cell is blanked out, a '0' appears left-justified in the cell AND the cell gets evaluated as a 1 rather than a 0.

     It seems like standard form for spreadsheets to evaluate a blank cell as a '0'.  This configuration works just fine in Excel.  I don't know what I can do to fix this problem. Please provide the simplest possible fix.  Thanks.

     --

     

  • Replied 8 September 2017, 2:19 pm EST

    Also, I forgot to mention, for the Number cells, I have set NumberOfDecimalPoints to 0 instead of -1 (not sure if that is the exact name of the property).

     

  • Replied 8 September 2017, 2:19 pm EST

    Also, please issue any code in VB.NET 2.5, as that is what I am using for my current project right now.

     

     

  • Replied 8 September 2017, 2:19 pm EST

    cruisefx,


    The PRODUCT function multiplies all the numbers given as arguments and returns the product.  If an argument to the PRODUCT function is an array or reference, only numbers in the array or reference are multiplied.  Empty cells, logical values, and text in the array or reference are ignored.  This is how Excel and OpenOffice define the PRODUCT function.


    For example, suppose cell A1 is empty and cell B1 contains the number 20.  The PRODUCT(A1,B1,0.25) would ignore cell A1 and just multiple cell B1 and 0.25 to get the result of 5.  You get the the same result in Excel and OpenOffice.


    If you need an empty cell to be treated as a zero in a multiplication operation then you have atleast two options.


    One option is to use the IF and ISBLANK functions to replace the empty cell with an zero in the call to the PRODUCT function.  For example...


        PRODUCT(IF(ISBLANK(A1),0,A1),IF(ISBLANK(B1),0,B1),0.25)


    Another option is to use the * operator.  For example...


        A1*B1*0.25


    The * operator treats empty cells as zero.  This is the same as in Excel and OpenOffice.

  • Replied 8 September 2017, 2:19 pm EST

    Ok, the ISBLANK along with the IF function allows it to work correctly, but without it, the PRODUCT function assesses the blank cell as a '1'.  This is not correct functionality, and should be remedied.

     

    --

     

  • Replied 8 September 2017, 2:19 pm EST

    Hello,

    We have added the above mentioned issue as feature request in Spread. And the request id for the same is #99916099.

    Thanks.

  • Replied 8 September 2017, 2:19 pm EST

    cruisefx,


    As I stated in my previous post, the PRODUCT function is designed to ignore empty cells passed by reference.  For example, if cell A1 is empty then PRODUCT(A1,B1,0.25) is equivalent to PRODUCT(B1,0.25).  The PRODUCT function is not treating empty cells as 1.  The PRODUCT function is skipping empty cells.  This is the way that the PRODUCT function is implemented in other major spreadsheet products including Microsoft Excel and OpenOffice.  We have to implement the PRODUCT function this way for compatibility with these other major spreadsheet products.


    I think the reason that Microsoft defined the PRODUCT function this way was so you could apply the PRODUCT function to a large block of cells that contained both number values and non-number values with the PRODUCT function returnimg the product of just the number values.  There are some other functions in Excel that behave the same way including SUM, COUNT, AVERAGE, AVEDEV, STDEV, VAR.

  • Replied 8 September 2017, 2:19 pm EST

    bobbyo:

    cruisefx,


    The PRODUCT function is not treating empty cells as 1.

    Have you tried it yourself?  Have you tried Farpoint vs. Excel on functionality on this matter?  It, indeed, is evaluating the blank cell as one in all my trials.

     

     Setup three cells.  First one for input (number, no decimal places), second (currency) 0,00, and third (number) 42.

    In the second cell, type in =PRODUCT([first cell], [third cell], .25)

    See if the currency equals zero after inputting 2 in the first cell, hitting enter, then backspacing it out,  then hitting enter again.  It is evaluating something in the formula as 1, where it should be zero.  Logic dictates that it must be the first cell that it is evaluating as zero.

     

    Excel handles this just fine.

    --

  • Replied 8 September 2017, 2:19 pm EST

    Hello Cruisefx,

    As explained by Bobbyo in previous post, PRODUCT function is ignoring the blank cells passed by reference rather than treating them as 1 .And Excel and Spread provides the same behavior.

    However, we tried setting up CurrencyCellType and NumberCellType as mentioned by you and PRODUCT function in CurrencyCellType evaluates to the correct value by ignoring the blank Cells.

    Thanks.

  • Replied 8 September 2017, 2:19 pm EST

    cruisefx,


    Yes, I have tested the PRODUCT function in Excel (version 2007) and OpenOffice (version 3.1).  Both Microsoft Excel and OpenOffice are producing the same results as Spread.


    Excel's documentation for the PRODUCT function includes the following remarks...


    "If an argument is an array or reference, only numbers in the array or reference are multiplied.  Empty cells, logical value, and text in the array or reference are ignored."


    Excel's documentation makes it clear that the PRODUCT function will exclude empty cells from the multiplication operation.


    Take the example that you gave.  When cell A1 is empty and cell C1 contains value 42, the evaluation will be,,,


        =PRODUCT(A1,C1,0.25)
        =PRODUCT(C1,0.25)
        =PRODUCT(42,0.25)
        =21.5


    When you tested the multiplication in Excel, did you use the PRODUCT function or the * operator?  As I mentioned in an earlier post, the PRODUCT function and the * operator treat empty cells differently.  The PRODUCT function ignores empty cell.  The * operator treats empty cells as zero.


    In your example, you can get the results that you want by replacing the PRODUCT function with the * operator.  When cell A1 is empty and cell C1 contains 42, the evaluation will be...


        =A1*C1*0.25
        =0*42*0.25
        =0

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels