Is this a case for an IF statement?

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)

Since you’ll be comparing the same value to four known values, this seems like you want to use a SWITCH() uses.

SWITCH(
   {Qtr},
   '1', {Extrachildren} * MIN(({Weeks FM/Zoom} + {WrrksSR}), 10) * 5,
   '2', {Extrachildren} * MIN(({Weeks FM/Zoom} + {WrrksSR}), 10) * 3,
   '3', ...,
   '4', ...
)
1 Like

Thank you so much for what would appear to be a logical solution (that I would never have found) unfortunately it will not save.

(I reduced the Qtr options just to so that I could screen shot it.)

You had made a minor typo on weeks and I had sent a version when I had changed the labels of FM & SR but correcting these and it still will not save but I cannot see why.

Here is a copy of part of the live database as of this morning.


(you are welcome to have full access to it if it helps)

Any guidance would be greatly appreciated as I have no idea what I am doing wrong.
Many thanks in advance for your reply.

Phil

Hi Kamille,
I have tried marginally altering your suggestion a number of ways but I still can’t get it to save even when I tried it as just the 1 option.
Can you please tell me what I am doing wrong as I just can’t get it to work.
I fully expect the answer to be a simple one but I have no idea as to what needs to change.
Many thanks
Phil

I don’t see anything wrong with the syntax. Try wrapping the calculation bits in parenthesis.

({Extrachildren} * MIN(({Weeks FM} + {WeeksSR}), 10) * 5)

Otherwise test out the formula including only portions of that^ calculation in to see if it works. (Does it work if you just have {Extrachildren}? Does it work if you just have the MIN()?)

This is the first thing that I have done that has saved SWITCH( {Qtr},({Extrachildren} ))

Added 1 but it didn’t save. SWITCH( {Qtr}1,({Extrachildren} ))

Saved SWITCH( {Qtr},1,({Extrachildren} ))
Failed SWITCH( {Qtr},1,({Extrachildren}* MIN ))
Saved SWITCH( {Qtr},1,({Extrachildren} *MIN))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({Weeks FM}+{WeeksSR}), 10) * 5,))
))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({Weeks FM}+{WeeksSR}), 10) * 5,))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN))(({Weeks FM}+{WeeksSR}), 10) * 5,)))
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({Weeks FM}+{WeeksSR}), 10) * 5
Failed SWITCH( {Qtr},1,({Extrachildren} *MIN(({WeeksFM}+{WeeksSR}), 10) * 5)

On the positive 2 things have saved.

Jumping at straws I know but my commas are not as pronounced as yours, could this have anything to do with it?

Very happy to give you access to this database once I have deleted emails and phone numbers

Really do appreciate your help with this.

Phil
Would I add you but I don’t have your email

Looking at the alignment of the data in your columns, it looks like {Weeks FM} does not report as a number (noticed how it aligns to the left link string, not to the right like a number).

I believe you’re getting errors because you are trying to perform math on something that is technically not a “number”. Try VALUE({Weeks FM}) + VALUE({WeeksSR})

1 Like

You are correct as the Weeks FM has a formula to take numbers from a text field.(for a reason that I can’t even remember, but it has been working.)

Screenshot 2021-03-08 at 22.04.29

I have assumed your instructions of adding the word VALUE was to the Syntax that you sent me, so I tried it but it didn’t save.

It is likely that I have a bracket in the wrong place but I regret to being too old to stay up through the night trying different combinations.

I will try in the morning but if you see anything obvious that I have done wrong I would appreciate your guidance.

Thank you once again for your input.
Phil

Your MIN() function is missing the beginning parenthesis., and you have a random comma after the 5.

Thanks so much for your continued help and I am sorry for my ignorance.

Comma has gone.

I have put the ( in front of MIN and tried it without the ) and then putting the ) in every position I can think of but it is still not saving.

Do you have any other ideas I could try?

Many Many thanks for all your help.

Phil

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.

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

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.

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.

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

1 Like

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

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.

1 Like

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’

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

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
)

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