Skip to main content

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.


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!

Reply


Terms & Conditions