Forum Discussion

AndrewKonopacki's avatar
AndrewKonopacki
Qrew Member
3 years ago

Need some help, formula for calculating Significant Figures

I have a formula in excel that performs the task I need. When I build it in QuickBase I'm not getting the same results. I stripped it down to the basics, here are the two results. I need to return a value that is a significant figure of 2.

Excel (the way I want it to work)
Returns 30
=ROUND(26.6,-1)

QuickBase 
Returns 27
=ROUND(26.6,-1)

Any help would be greatly appreciated.

------------------------------
Andrew Konopacki
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    Never knew what that meant in Excel.

    Google says:
    Can you round to significant figures in Excel?
    For example, to round 2345678 down to 3 significant digits, you use the ROUNDDOWN function with the parameter -4, as follows: = ROUNDDOWN(2345678,-4). This rounds the number down to 2340000, with the "234" portion as the significant digits.

    ... When I get a chance I can respond, but it will take a formula as I doubt Quickbase supports that nuance of rounding to significant digits.




    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • AndrewKonopacki's avatar
      AndrewKonopacki
      Qrew Member
      3 years ago
      Thanks Mark. Yes, that is what I'm trying to accomplish in QB.

      Here is another formula I used in Excel that returns what I want but doesn't return the same value in QuickBase. 

      =ROUND([value],[figure]-(1+INT(LOG10(ABS([value])))))
      ​

      ------------------------------
      Andrew Konopacki
      ------------------------------
  • RaziD_'s avatar
    RaziD_
    Qrew Cadet
    3 years ago

    HI Andrew;
    If I understood correctly you want to round to 10's.
    You can try,

    Round([Number], 10)

    Round(26.6, 10) ==> returns 30

    I hope this helps :)



    ------------------------------
    Razi D.
    Desta Tech LLC
    razi@destatechs.com
    ------------------------------
    • AndrewKonopacki's avatar
      AndrewKonopacki
      Qrew Member
      3 years ago
      Correct. I came to the same conclusion last night after giving it some more thought. 95% of my values will be less than 10k, but would still need to be able to stay within my 2 sig fig parameter if the number got to 100k or more. This works fine for my application. Thanks again for both of your inputs! 

      If([M1 - Spores m3]<100, Round([M1 - Spores m3],1),
      [M1 - Spores m3]<999, Round([M1 - Spores m3],10),
      [M1 - Spores m3]<9999, Round([M1 - Spores m3],100),
      [M1 - Spores m3]<99999, Round([M1 - Spores m3],1000),
      [M1 - Spores m3]>99999, Round([M1 - Spores m3],10000))

      ------------------------------
      Andrew Konopacki
      ------------------------------