Simplifying and completing a formula

Ask the community Base design
#1

I have a formula that correctly shows the cost of having 1 child in a class. When the parent has 2 or more children the cost is £5 per extra child per week.
They could be in either Weeks FM or WeeksSR.
So far I have a column showing “extraChildren” / number of weeks /class fm/ class sr/ but my formula is wrong and I have no idea what it should be.

Screenshot 2020-12-10 at 14.17.11
Screenshot 2020-12-10 at 14.17.111564×304 41.1 KB

Once I have the correct total I will need to add that to the combined Total Amount

Screenshot 2020-12-10 at 14.28.26
Screenshot 2020-12-10 at 14.28.26888×322 34.2 KB

The Amount formula is already very complex so how would I add it.IF({No of weeks FM}, VALUE(RIGHT({No of weeks FM}, LEN({No of weeks FM})- FIND(“£”, {No of weeks FM}))), 0)+IF({Zoom}, VALUE(RIGHT({Zoom}, LEN({Zoom})- FIND(“£”, {Zoom}))),0)+IF({Party}, VALUE(RIGHT({Party}, LEN({Party})- FIND(“£”, {Party}))), 0)+IF({No of weeks S&R}, VALUE(RIGHT({No of weeks S&R}, LEN({No of weeks S&R})- FIND(“£”, {No of weeks S&R}))), 0)

Any help you can give me on this would be greatly appreciated as no matter what I read about formula, I am afraid I can’t seem to follow the logic.

#2

Philip, try using the following for your extra children calculation…

Extrachildren * ({Weeks FM} + {WeeksSR}) * 5

I left out anything about your Sibling*£5 column.

You could create a new field that adds the above to your current “Amount” field, to get a Total. Or, you could add the above to your Amount field with a “+” before the add’l part of the formula.

Does that work?

#3

Firstly thank you so much for your input.
I have literally spent all day today, watching Airtable videos and support files but came nowhere near your solution which certainly totals correctly.
I have tried to add it to the Amount formula but failed so far.
I will try again in the morning but I have to confess to being very tired now.
Should the “+”{Total for Extra-children} be at the front or the end of the existing formula and should there be additional brackets?
I can’t tell you how much I appreciate your help.
Thank you Phil

#4

You’re welcome Philip. Leave the curly braces around the field name, but take the double quotes away from the + sign. Leaving it at the end is fine.

#5

My niece wants to go live with this database on Monday and I currently think that it is now possible.(hopefully no more cries for help)

You will be pleased to know that at 73 I am unlikely to need to be creating additional Airtable solutions!

Airtable continues to amaze my how easy it is to use (except for the Formula Field). But thanks to people like you spending your own time helping others is fantastic.

I do want to thank you again for making the impossible, possible!
Have a great Christmas and Hopefully a save 2021

1 Like
#6

One last twist in the calculation of extra children is that if the parent books a second (or more) child for the full 11 week term then an extra discount of £5 =to (1 week) is given per the extra child/children.

I feel sure it is an IF statement but not sure where to put it or how to write it.
Can you help me once again please?
Thanks in anticipation of a solution.
Phil

#7

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.