Forum Discussion

RebeccaKnecht__'s avatar
RebeccaKnecht__
Qrew Member
2 years ago

FOrmulas

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
------------------------------

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago
    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
    ------------------------------
    • RebeccaKnecht__'s avatar
      RebeccaKnecht__
      Qrew Member
      2 years ago
      The Mileage Cost Total is a numeric formula and doesnt have the option for treat as 0.

      ------------------------------
      Rebecca Knecht, MS, ACSM-CEP
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew #1 Challenger
        2 years ago
        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
        ------------------------------