Forum Discussion

MichelleCoslet's avatar
MichelleCoslet
Qrew Assistant Captain
5 years ago

Formula help

I am trying to create a numeric formula. Hope someone can assist

If [ORDER QTY] is between 2 and 8, [SAMPLE SIZE] equals "2",

If [ORDER QTY] is between 9 and 15, [SAMPLE SIZE] equals "3",
If [ORDER QTY] is between 16 and 25, [SAMPLE SIZE] equals "5",
If [ORDER QTY] is between 26 and 50, [SAMPLE SIZE] equals "8",
If [ORDER QTY] is between 51 and 90, [SAMPLE SIZE] equals "13",
If [ORDER QTY] is between 91 and 150, [SAMPLE SIZE] equals "20",
If [ORDER QTY] is between 151 and 280, [SAMPLE SIZE] equals "32",
If [ORDER QTY] is between 281 and 500, [SAMPLE SIZE] equals "50",
If [ORDER QTY] is between 501 and 1200, [SAMPLE SIZE] equals "80",
If [ORDER QTY] is greater than 1201, [SAMPLE SIZE] equals "125".



------------------------------
mkosek mkosek
------------------------------
Formulas and functions
  • AustinK's avatar
    AustinK
    Qrew Commander
    5 years ago
    What is the goal of this formula? If you can write out the thing you are trying to solve that may be easier. 

    Looking at this formula it seems like what you are wanting it to do is set another field to be a certain number? If I have this right then your formula is not correct as you cannot just force another field to change the data in it like that. One way it would work is you would make [Sample Size] your formula field and have something like this in there:

    If(
    [ORDER QTY] >= 2 and [ORDER QTY] <= 8, 2, 
    [ORDER QTY] >= 9 and [ORDER QTY] <= 15, 3, 
    [ORDER QTY] >= 16 and [ORDER QTY] <= 25, 5, 
    [ORDER QTY] >= 26 and [ORDER QTY] <= 50, 8
    )

    I think that is what you are after. Then this [Sample Size] field would display the number based on the Order Quanity.

    Marks formula also works. I just like to be really explicit with the numbers in my formulas usually. They should both work the exact same in the end I think.
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Unlike Excel, you typically only need one IF.  Quickbase will evaluate the conditions in sequence and stop at the first  one that is true.

    This would be a formula for the field [Sample Size]


    If(
    [ORDER QTY] <= 1, null, 
    [ORDER QTY] <= 8,2,
    [ORDER QTY] <= 15,3,
    etc
    [ORDER QTY] <= 1200, 80,
    [ORDER QTY] >= 1201, "125")



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------