Help with a formula:gratuity amount

Slow Learner · 01-12-2006, 02:35 PM

Hi there can someone help me...
worksheet has 4 colums, column 1 contains full price of article (known
amount), column 2 has tax amount (not always known), column 3 has gratuity
amount (sometimes known), column 4 has total without tax but with gratuity.

Tax, if unknown, is calculated at 7% of amount without gratuity.

What would the formulas be to have my calculations become automatic on input
of full amount?

Thank you

Gary L Brown · 01-12-2006, 03:15 PM

Assume...
Cell A2 - Full Price
Cell B2 - Tax
Cell C2 - Gratuity
Cell D2 - Total
-------------------------
B2 = A2 * .07
C2 = A2 * .15 <=assume 15% gratuity
D2 = A2 + C2

--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

"Slow Learner" wrote:

> Hi there can someone help me...
> worksheet has 4 colums, column 1 contains full price of article (known
> amount), column 2 has tax amount (not always known), column 3 has gratuity
> amount (sometimes known), column 4 has total without tax but with gratuity.
>
> Tax, if unknown, is calculated at 7% of amount without gratuity.
>
> What would the formulas be to have my calculations become automatic on input
> of full amount?
>
> Thank you

**wjohnson** · 01-12-2006, 03:22 PM

I would add 1 more column which would be Column E and would be titled "Total With Tax + Gratuity.
Your Column D would then read as follows: =(A2+C2)
Then Column E would read as follows: =IF(B2="",(A2*7%)+D2,(A2*B2)+D2)
Which reads as follows: If B2 is Empty then A2*7%+D2, IF False - Then A2*B2+D2 this will give you the total of Cost + Gratuity + Tax. If you want to use a % in Cell B2 then you will need to format it as %

pinmaster · 01-12-2006, 03:45 PM

Try this:
in D1
=IF(A1>0,IF(B1>0,A1+(A1*B1)+C1,A1+(A1*7%)+C1),0)
copied down

A1 - amount
B1 - if left empty then the tax will be 7%, otherwise tax in B1
C1 - gratuity
D1 - total

Hope this helps!
JG

"Slow Learner" wrote:

> Hi there can someone help me...
> worksheet has 4 colums, column 1 contains full price of article (known
> amount), column 2 has tax amount (not always known), column 3 has gratuity
> amount (sometimes known), column 4 has total without tax but with gratuity.
>
> Tax, if unknown, is calculated at 7% of amount without gratuity.
>
> What would the formulas be to have my calculations become automatic on input
> of full amount?
>
> Thank you

pinmaster · 01-12-2006, 03:50 PM

Oops.....misread your post a little, it should be:

=IF(A1>0,IF(B1>0,A1+B1+C1,A1+(A1*7%)+C1),0)
copied down

A1 - amount
B1 - tax amount
C1 - gratuity
D1 - total
if B1 is not given then the tax will be 7% of A1

Regards
JG

"Slow Learner" wrote:

> Hi there can someone help me...
> worksheet has 4 colums, column 1 contains full price of article (known
> amount), column 2 has tax amount (not always known), column 3 has gratuity
> amount (sometimes known), column 4 has total without tax but with gratuity.
>
> Tax, if unknown, is calculated at 7% of amount without gratuity.
>
> What would the formulas be to have my calculations become automatic on input
> of full amount?
>
> Thank you