Discussions

 View Only
  • 1.  FOrmulas

    Posted 08-16-2022 13:08
    Having trouble getting a formula to do what i want.

    I want to add up the following items, but one of the items is already a formula and I when i out it in the formula to get a total unless that one has a number no total shows. How can I get the formula to say if nothing is in that spot to just assume its zero.

    example: Hotel expenses + Flat rate + Mileage Cost total = Total

    Mileage cost total is a numeric formula that is If([Mileage] >15, [Mileage Cost]). Hotel expenses and flat rate are both just numbers type into the field. Hoept his makes sense. Any duggestions are welcome

    ------------------------------
    Rebecca Knecht, MS, ACSM-CEP
    ------------------------------


  • 2.  RE: FOrmulas

    Posted 08-16-2022 14:31
    np,
    Just go to the field properties for each field and check the box called "Treat blank as zero"

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: FOrmulas

    Posted 08-16-2022 14:38
    The Mileage Cost Total is a numeric formula and doesnt have the option for treat as 0.

    ------------------------------
    Rebecca Knecht, MS, ACSM-CEP
    ------------------------------



  • 4.  RE: FOrmulas

    Posted 08-16-2022 15:03
    Can help you correct your formula if you post it but in general when you make a formula you need to define what happens if the IF is not true.

    For example you might have a Formula like

    IF([Eligible for Reimbursement]=true, [rate] * [mileage claimed])  

    The formula above is silent if [Eligible for Reimbursement] is false then the result is null.  Bernal and QuickBase behaves a bit like  @ n/a in Excel  - that uses that in the formula also becomes @ n/a.

    so you have two choices.  Either define what happens when the formula is not true.

    IF([Eligible for Reimbursement]=true, [rate] * [mileage claimed], 0)  

    ​​​or else you can make your formula messy like this.

    Nz([Hotel expenses]) + Nz([Flat rate]) + Nz(Mileage Cost total])

    but I personally hate to bugger up my aps full of Nz's.  That means to trat null as zero.

    If the components of your formula are the result of summary fields, then edit the field properties of the summary fields to checkbox the setting for "treat blank as zero". 


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: FOrmulas

    Posted 08-19-2022 13:44
    That helped thanks!

    ------------------------------
    Rebecca Knecht, MS, ACSM-CEP
    ------------------------------