HI I have just began to use Excel for invoices. I have set my Tax rate at
17.5%. but we use two tax rates here, the other is 5%.
How can I do this on one invoice.
Eg: 150+17.5%
150+5%

Maybe something like:

=150*17.5% = 26.25
=150*5% = 7.5
=150+(150*17.5%) = 176.25
=150+(150*5%) = 157.5
or
=150+(150*22.5%) = 183.75

HTH
JG

"PJOS11" wrote:

HI I have just began to use Excel for invoices. I have set my Tax rate at
17.5%. but we use two tax rates here, the other is 5%.
How can I do this on one invoice.
Eg: 150+17.5%
150+5%

"PJOS11" wrote:
I have just began to use Excel for invoices. I have set
my Tax rate at 17.5%. but we use two tax rates here,
the other is 5%. How can I do this on one invoice.
Eg: 150+17.5%
150+5%

Your statement is not clear to me. First, you say that
you use 17.5%; then you say you also(?) use 5%. Do
you use one rate under one condition and the other
rate under another condition? Do you charge the total
of the two tax rates? Do use one tax rate up to some
amount and the other tax rate for the remaining amount?

If you clarify how the two tax rates are applied, I am
sure your question can be answered easily.

Experiment with something like this:

1) Create a rate table in a new worksheet (I'll assume Sheet2)

Col_A Col_B
Row_1 RateID Rate
Row_2 Rate1 17.5%
Row_3 Rate2 5.0%

Select A2:B3 range and name it:
InsertNameDefine
Names in workbook: LU_Rates
Refers to: (already selected)

Now, go to your invoice sheet.
I'll assume your total sales amount is in cell H9 and your rate amount goes
in cell H10.

Select F10
DataValidation
Allow: List
Source: =OFFSET(LU_Rates,0,0,,1)

G10: =IF(ISBLANK(G10),0,VLOOKUP(G10,LU_Rates,2,0))

H10: =H9*G10


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"PJOS11" wrote:

HI I have just began to use Excel for invoices. I have set my Tax rate at
17.5%. but we use two tax rates here, the other is 5%.
How can I do this on one invoice.
Eg: 150+17.5%
150+5%

Hi

Presumably you have a field where you are entering the VAT Code for the
transaction.
In the UK valid codes would "S", "L", "Z", "E", "O" with rates of 17.5%,
5%, 0%, 0%, 0% respectively.

If you are wanting to do the calculation on a line by line entry on your
invoice, and assuming Column C contains your Item Value, and column D
the appropriate VAT rate (letter), then in cell E2
=IF(D2="S",C2*17.5%,IF(D2="L",C2*5%,C2)) to show just the VAT amount or
=IF(D2="S",C2*1+17.5%,IF(D2="L",C2*1+5%,C2)) to show the amount uplifted
by the appropriate VAT percentage.

If you are just showing the VAT as a total at the bottom of the invoice,
and assuming the values are in the range C2:C20, with VAT code in
D2:D20, then
=SUMIF(D2:D20,"S",C2:C20)*17.5%+SUMIF(D2:D20,"L",C ":C20)*5%

--
Regards

Roger Govier


"
wrote in message
...
"PJOS11" wrote:
I have just began to use Excel for invoices. I have set
my Tax rate at 17.5%. but we use two tax rates here,
the other is 5%. How can I do this on one invoice.
Eg: 150+17.5%
150+5%

Your statement is not clear to me. First, you say that
you use 17.5%; then you say you also(?) use 5%. Do
you use one rate under one condition and the other
rate under another condition? Do you charge the total
of the two tax rates? Do use one tax rate up to some
amount and the other tax rate for the remaining amount?

If you clarify how the two tax rates are applied, I am
sure your question can be answered easily.

Hi you all seem like experts!
If I am invoicing say £1000. Seven hundred of this maybe charged at 17.5%
and the remaining £300 would be charged at 5%. I can enter two tax rates at
the bottom but this adds the 17.5% and the 5% on £1000 giving a total of
£1167.50 instead of £1137.50.
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

"Ron Coderre" wrote:

Experiment with something like this:

1) Create a rate table in a new worksheet (I'll assume Sheet2)

Col_A Col_B
Row_1 RateID Rate
Row_2 Rate1 17.5%
Row_3 Rate2 5.0%

Select A2:B3 range and name it:
InsertNameDefine
Names in workbook: LU_Rates
Refers to: (already selected)

Now, go to your invoice sheet.
I'll assume your total sales amount is in cell H9 and your rate amount goes
in cell H10.

Select F10
DataValidation
Allow: List
Source: =OFFSET(LU_Rates,0,0,,1)

G10: =IF(ISBLANK(G10),0,VLOOKUP(G10,LU_Rates,2,0))

H10: =H9*G10


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"PJOS11" wrote:

HI I have just began to use Excel for invoices. I have set my Tax rate at
17.5%. but we use two tax rates here, the other is 5%.
How can I do this on one invoice.
Eg: 150+17.5%
150+5%

How do you determine what amount is taxed at 17.5% and what amount it taxed
at 5%? Meanwhile you can try something like:

=IF(A1700,700+17.5%+((A1-700)+5%),A1+17.5%)

Hope this helps!
JG

"PJOS11" wrote:

Hi you all seem like experts!
If I am invoicing say £1000. Seven hundred of this maybe charged at 17.5%
and the remaining £300 would be charged at 5%. I can enter two tax rates at
the bottom but this adds the 17.5% and the 5% on £1000 giving a total of
£1167.50 instead of £1137.50.
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

"Ron Coderre" wrote:

Experiment with something like this:

1) Create a rate table in a new worksheet (I'll assume Sheet2)

Col_A Col_B
Row_1 RateID Rate
Row_2 Rate1 17.5%
Row_3 Rate2 5.0%

Select A2:B3 range and name it:
InsertNameDefine
Names in workbook: LU_Rates
Refers to: (already selected)

Now, go to your invoice sheet.
I'll assume your total sales amount is in cell H9 and your rate amount goes
in cell H10.

Select F10
DataValidation
Allow: List
Source: =OFFSET(LU_Rates,0,0,,1)

G10: =IF(ISBLANK(G10),0,VLOOKUP(G10,LU_Rates,2,0))

H10: =H9*G10


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"PJOS11" wrote:

HI I have just began to use Excel for invoices. I have set my Tax rate at
17.5%. but we use two tax rates here, the other is 5%.
How can I do this on one invoice.
Eg: 150+17.5%
150+5%

Hi

You need to apply Ron's formula, not to the total of £1000, but to the
subtotal of the lines at the Standard rate and then to the subtotal of
the lines at the Low rate, then add the tow values together.

Alternatively, use my SUMIF formulae.

--
Regards

Roger Govier


"PJOS11" wrote in message
...
Hi you all seem like experts!
If I am invoicing say £1000. Seven hundred of this maybe charged at
17.5%
and the remaining £300 would be charged at 5%. I can enter two tax
rates at
the bottom but this adds the 17.5% and the 5% on £1000 giving a total
of
£1167.50 instead of £1137.50.
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

"Ron Coderre" wrote:

Experiment with something like this:

1) Create a rate table in a new worksheet (I'll assume Sheet2)

Col_A Col_B
Row_1 RateID Rate
Row_2 Rate1 17.5%
Row_3 Rate2 5.0%

Select A2:B3 range and name it:
InsertNameDefine
Names in workbook: LU_Rates
Refers to: (already selected)

Now, go to your invoice sheet.
I'll assume your total sales amount is in cell H9 and your rate
amount goes
in cell H10.

Select F10
DataValidation
Allow: List
Source: =OFFSET(LU_Rates,0,0,,1)

G10: =IF(ISBLANK(G10),0,VLOOKUP(G10,LU_Rates,2,0))

H10: =H9*G10


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"PJOS11" wrote:

HI I have just began to use Excel for invoices. I have set my Tax
rate at
17.5%. but we use two tax rates here, the other is 5%.
How can I do this on one invoice.
Eg: 150+17.5%
150+5%

Perhaps you could try this:

Flag each item's tax rate status to the right of the sale amount (Reg Sale,
Other Sale).

Then, at the bottom of the invoice you'd have something like the following
(using my previously posted example):

H9: =SUMIF(I2:I8,"Reg Sale",H2:H8)
I9: Reg Sale Total
H10: =H9*VLOOKUP("Rate1",LU_Rates,2,0)
I10: Reg Tax
H11: =SUMIF(I2:I8,"Other Sale",H2:H8)
I11: Other Sale Total
H12: =H9*VLOOKUP("Rate2",LU_Rates,2,0)
I12: Other Tax
H13: =SUM(H10:H12)
I13: Net Invoice

Something you can work with, maybe?

***********
Regards,
Ron

XL2002, WinXP-Pro


"PJOS11" wrote:

Hi you all seem like experts!
If I am invoicing say £1000. Seven hundred of this maybe charged at 17.5%
and the remaining £300 would be charged at 5%. I can enter two tax rates at
the bottom but this adds the 17.5% and the 5% on £1000 giving a total of
£1167.50 instead of £1137.50.
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

"Ron Coderre" wrote:

Experiment with something like this:

1) Create a rate table in a new worksheet (I'll assume Sheet2)

Col_A Col_B
Row_1 RateID Rate
Row_2 Rate1 17.5%
Row_3 Rate2 5.0%

Select A2:B3 range and name it:
InsertNameDefine
Names in workbook: LU_Rates
Refers to: (already selected)

Now, go to your invoice sheet.
I'll assume your total sales amount is in cell H9 and your rate amount goes
in cell H10.

Select F10
DataValidation
Allow: List
Source: =OFFSET(LU_Rates,0,0,,1)

G10: =IF(ISBLANK(G10),0,VLOOKUP(G10,LU_Rates,2,0))

H10: =H9*G10


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"PJOS11" wrote:

HI I have just began to use Excel for invoices. I have set my Tax rate at
17.5%. but we use two tax rates here, the other is 5%.
How can I do this on one invoice.
Eg: 150+17.5%
150+5%

"PJOS11" wrote:
If I am invoicing say £1000. Seven hundred of this
maybe charged at 17.5% and the remaining £300
would be charged at 5%.
[....]
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

If the tax rule is as simple as the above, the following
should suffice (assume the invoice amount is in A1):

=A1 + 17.5%*MIN(700,A1) + 5%*MAX(0,A1-700)

However, responses from others lead me to suspect
that your description of the tax rule is misleadingly
over-simplified. GIGO.

I`m a Heating engineer and carry out high efficency heating systems so not
every job is the same price. The heating controls are charge at 5% and the
rest of the work at 17.5%. So the amounts will change every job
Is it possible to do this on exel sales invoices?

" wrote:

"PJOS11" wrote:
If I am invoicing say £1000. Seven hundred of this
maybe charged at 17.5% and the remaining £300
would be charged at 5%.
[....]
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

If the tax rule is as simple as the above, the following
should suffice (assume the invoice amount is in A1):

=A1 + 17.5%*MIN(700,A1) + 5%*MAX(0,A1-700)

However, responses from others lead me to suspect
that your description of the tax rule is misleadingly
over-simplified. GIGO.

I'm assuming you have an "Item Description" column on your invoice form. If
so then what you could do is precede your Heating control item with the
letters "HC-", then use the formula below in the tax amount column:
=IF(LEFT(C1,2)="HC",D1*5%,D1*17.5%)

Also I would set up 2 cells to represent your tax rates, name each cell
....say tax1 and tax2 and use that in your formula, that way if the tax rates
changes down the road you would only have to update those 2 cells. So the
formula would look like:
=IF(LEFT(C1,2)="HC",D1*tax2,D1*tax1)

Note. to name a cell, select it and hit CTRL+F3 and type the name in the
first box, hit OK.

Hope it's something you can use!
JG

"PJOS11" wrote:

I`m a Heating engineer and carry out high efficency heating systems so not
every job is the same price. The heating controls are charge at 5% and the
rest of the work at 17.5%. So the amounts will change every job
Is it possible to do this on exel sales invoices?

" wrote:

"PJOS11" wrote:
If I am invoicing say £1000. Seven hundred of this
maybe charged at 17.5% and the remaining £300
would be charged at 5%.
[....]
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

If the tax rule is as simple as the above, the following
should suffice (assume the invoice amount is in A1):

=A1 + 17.5%*MIN(700,A1) + 5%*MAX(0,A1-700)

However, responses from others lead me to suspect
that your description of the tax rule is misleadingly
over-simplified. GIGO.

What you need is a way to mark you items taxed at 5% or 17.5%, so if you have
an "item description" column, what you could do is precede your heating
controls items with the letters "HC-", then use
=SUM(IF(LEFT(C1:C20,2="HC",D1:D20)) .... sum of items taxed at 5%
=SUM(D1:D20)-SUM(IF(LEFT(C1:C20,2)="HC",D1:D20)) ...sum of items taxed at
17.5% both formulas need to be entered using SHIFT+CTRL+ENTER.
where C1:C20 is your description column and D1:D20 is item cost.

Put those formulas into seperate cells then tax rate into the ajacent cells
and tax amount into the next ajacent cells.


Hope it's something you can use!
JG

"PJOS11" wrote:

I`m a Heating engineer and carry out high efficency heating systems so not
every job is the same price. The heating controls are charge at 5% and the
rest of the work at 17.5%. So the amounts will change every job
Is it possible to do this on exel sales invoices?

" wrote:

"PJOS11" wrote:
If I am invoicing say £1000. Seven hundred of this
maybe charged at 17.5% and the remaining £300
would be charged at 5%.
[....]
£700+17.5%=£822.50
£300+5%= £315.00
Total £1137.50

If the tax rule is as simple as the above, the following
should suffice (assume the invoice amount is in A1):

=A1 + 17.5%*MIN(700,A1) + 5%*MAX(0,A1-700)

However, responses from others lead me to suspect
that your description of the tax rule is misleadingly
over-simplified. GIGO.

"PJOS11" wrote:
I`m a Heating engineer and carry out high efficency
heating systems so not every job is the same price.
The heating controls are charge at 5% and the rest
of the work at 17.5%. So the amounts will change
every job.

Oh, that is a very different story. Let's assume that the
cost of the heating controls is in A1, and the cost of
everything else is in B1. Then C1 might be the tax,
computed as:

=5%*A1 + 17.5%*B1

and D1 might be the total invoice, computed as:

=A1 + B1 + C1

Is that what you are looking for? It seems pretty remedial.
Nothing wrong with that. It just me suspicious that I
am not fully understanding your requirements.

PS: I am not familiar with your monetary system or
your sales tax regulation, but for the US, I would add
some rounding, for example:

=ROUND(5%*A1,2) + ROUND(17.5%*B1,2)