Date calculations to determine "yesterday"

[E77en]

I'm a bit stuck. 😅 

The end goal is that I can determine how much everyone should be paying in a house depending on the conditions of the room, the minimum price of payment, and the number of people within a room. 

--

Information: 

• I have 1 airtable with different tables. Table one is called: Room (https://airtable.com/appLy4v2lQcr2yxf8/shrBKTdZxtE96rQOr)
  
  Within this table, I have 7 rows with different rooms. I have another table that has different conditions where I can rank the importance/benefits of a specific room. This eventually ends up with a total number of conditions which I then have a script that makes a ranking out of that in depending on the specific rooms.
  
  Within the Table Room I also have a column that shows he number of people that stay in that room. 
  
  
• Then I have another table called: %
  (https://airtable.com/appLy4v2lQcr2yxf8/shr2ZXmJPEn7Te53l)
  
  Where I have a table that only shows the percentage a specific ranking should be paying for. So if a room is ranked number 3, I need a formula (I guess) that can look this up and  shows it should be: 3A 17.85% 5.176,50. 
  
  
• Finally I have a table called: Min rent pp
  (https://airtable.com/appLy4v2lQcr2yxf8/shr77owxWD26H6lBZ)
  I want that each person atleast pays a specific price which is shown in here. 

----

Example:

An example of how one of the results should be: 

Room: Apartment is rank 1. So the percentage that it should look at is: 25.03%, which is 7.258,70. The room is for 2 people so: 7.258,70/2= 3.629,35. Plus the minimum 3.629,35+3.000 = 6.629,35. So per person in the room Apartment a person should pay: 6.629,35. 

How do I get this to work? 😅 

[seferiannie]

Hello. your tables have a design issue and then the formula will be much easy.

you need to have a key between them all, which is not the case, since in the % you have them as 1A, while in the others it is 1

also, in Room, you have the value per person already at 8K, while in the example you said 7K

unify the primary keys (in this example i am assuming 1 and not 1A, and you can add the % and room in the same table based on what you're explaining - also having a better naming of the fields can help you

then, to solve your problem, bring the relevant fields into the main table (with linked records)

then the formula goes:

{Room price (from %)}/{nr of people}+{Minimum (from pp)}