Is this a case for an IF statement?

[Philip_Barber]

My database has run well for 3 months but now I need to change the discount structure. I have added a Column and called it Qtr and each record will be either 1,2,3,or 4 the idea being that the discount offered for that Qtr could be different.
So far I have two different discount formula but how do I add the qtr to the formula

![Screenshot 2021-03-06 at 15.11.14|700x210]

Formula

![Screenshot 2021-03-06 at 15.12.22|700x165]

or

(upload://pdKo1JReaKkSalwdO6FRbe3o0Lb.png)
(upload://hc9LcneLXO1dfccFOyXw0GxLXND.png)

[Kamille_Parks]

In that screenshot, you still are missing the opening parenthesis for MIN(). You wrote the word MIN and then added a space and then started another function.

[Philip_Barber]

Will I ever get this correct?

As you can see I have tried a number of minor changes but all are wrong and will not save.

I keep reading your note but I am afraid it is not clear to me as clearly I am not understanding something that I guess is obvious to you.

Would you mind typing the complete change I should make.

Once again thank you for helping me.

Phil

[Philip_Barber]

Now mid afternoon and I have the following formula that does save.
SWITCH( {Qtr},1,({Extrachildren}* MIN(({Weeks FM}+{WeeksSR}), 10) * 5))
Unfortunately that is all it does.
The column is completely blank.
In theory I would have thought that as I am comparing the test system current Qtr with the live Current Qtr, then the results would be the same but as I said there is nothing now not even a £0

There is no point testing other qtrs until at least this one gives the same results.

[ScottWorld]

Unfortunately and very frustratingly, the formula box in Airtable only allows straight quotation marks, not stylized quotation marks (i.e. curly quotation marks).

Note that your quotation marks are stylized instead of being straight.

[Philip_Barber]

Thanks Scott. On my last effort I had missed the quotation marks completely so your suggestion was both timely and correct.

[Philip_Barber]

At last the first of the SWITCH calculations is working.

I have now tried to enter the second of the discount structures but it doesn’t save so yet again I am doing something wrong but I have no idea as to what.

Is there anybody who can point out the error of my ways!!

[Kamille_Parks]

You need to pay closer attention to your parentheses. Once again, you’re missing an opening parentheses. Since there are two closing parentheses after the * 3, then there should be one opening parentheses in front of the beginning of that argument, which is {Extrachildren}.

Parentheses have two purposes in formulas

• They allow functions to work. There are very, very few functions where you don’t need to put anything within the parenthesis, the only example I can think of is TODAY(). All other functions, including MIN() require you to enter some sort of argument between the parantheses (i.e. what are you trying to find the minimum between?) and you ALWAYS must have an opening and closing parantheses for them in the style of NAME_OF_FUNCTION(). This applies to no-argument functions like TODAY().
• They help group portions of the formula for your benefit to make them a little easier to read. Airtable does not actually require them to be there in this instance, but if you include one half of a parentheses pair you MUST include the other half as well.

The reason I haven’t written out the full correct formula for you is because I’m trying to force you to work through the basics. If there is something specific about formula syntax that isn’t clicking, please state it so I can explain.

[Philip_Barber]

I do really appreciate how you are trying to teach me. I am just aware how much of your time I am taking when it is unlikely at 73 I will ever do another database, I certainly will not say I can help my niece again!!! Thats not strictly true but I am getting frustrated.

After getting the Qtr 1 to work I had two closing parenthesis.
Onto Qtr2.
Looking at your previous examples it appeared I needed to do away with those closing parenthesis and put in a coma as the formula was to move onto the Qtr 2. I have now tried adding a parenthesis before Extrachildren (as instructed) so my logic says that I need to add 3 closing parenthesis (2 from the 1st qtr and one from the one I added before Extrachildren. Clearly I am wrong as it doesn’t save.
But why?
And why does the parenthesis become before Extrachildren and not before ‘2’

[Kamille_Parks]

Here is the pattern that all SWITCH() functions follow.

SWITCH(
   [the value you're trying to test],
   [the 1st possible value], [what to insert if the 1st value is true],
   [the 2nd possible value], [what to insert if the 2nd value is true],
   [the 3rd possible value], [what to insert if the 3rd value is true],
   ...
   [optional, one last 'what to insert' if none of the other values are true]
)

Philip_Barber:

why does the parenthesis become before Extrachildren and not before ‘2’

To answer your’ question, ‘2’ is your “the 2nd possible value”, and {Extrachildren}… is the “what to insert if the 2nd value is true”. If you put the parenthesis comes before ‘2’, you are grouping both “value” and “what to insert”. Those two need to be separate arguments for this function.

So, walking through your use case, here’s the structure:

SWITCH(
   {Qtr},
   "1", (insert x1),
   "2", (insert x2)
)

x1 == {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5
x2 == {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 11) * 3

Again, you don’t have to wrap “x1” or “x2” within parenthesis, but if you did want to, your formula should be:

SWITCH(
   {Qtr},
   "1", ({Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5),
   "2", ({Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 11) * 3)
)

If you didn’t want the wrapped parenthesis, it would be:

SWITCH(
   {Qtr},
   "1", {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5,
   "2", {Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 11) * 3
)

[Philip_Barber · Accepted solution]

I know I had to be spoon fed but I can’t tell you how much I appreciate the time and effort you have put in with me.
Naturally your last answer has saved correctly and even calculated correctly.
You will not be surprised but I am certainly relieved.
I will read your last comments again tomorrow and try to follow the logic and fully understand what you wrote but for now I want a glass of wine and to close my eyes.
Thank you and good night. I might even be able to sleep tonight knowing the problem is solved(well at least on the test system).