Embarrassingly silly question (tax Formula from total)

[Lost-Merovingia]

I have a table of invoices for hotels where sometimes not all the data comes through, so I get the Total, but not the rate of the items, or the tax.

I’m going to set up an automation that reserve calculates, but it’s breaking my brain.

So I need a formula that does these:
{Total} - {tax}= subtotal
{subtotal} - {fee} = Hotel Fare
[Hotel fare} / {nights} = nightly rate

It seems easy, but it’s not working for me. I’m not sure what I’m doing wrong.

Airtable

Grid view - Airtable

Explore the "Grid" view on Airtable.

[augmented]

Let’s consider Guest Two as an example.

subtotal = Total / (1+{Tax Rate}/100)
subtotal = 664.7 / (1+15/100)
subtotal = 578

tax = Total - subtotal
tax = 86.7

hotel fare = subtotal - cleaning
hotel fare = 578 - 60
hotel fare = 518

hotel nightly rate = 518 / 2
hotel nightly rate = 259

So, we can back out all of the numbers, but we can’t have the fields for these values be formulas sometimes, and numeric others. So, for all of the fields that you’re going to have empty sometimes, you need additional formula fields that calculate everything like I did above.

I hope that’s clear and mistake free (and that I understood your problem correctly). Good luck!