Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Embarrassingly silly question (tax Formula from total)

239 1
Showing results for 
Search instead for 
Did you mean: 

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.

1 Reply 1

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!