Formulating taxes assistance

[Johnathan_Jeremy]

I am creating a expense form with excel. I want to use the value of a drop down on cell E6 of either P,G, GP. These represent GST, PST, or GST&PST depending on the good / service. Then is cell F6 would be GST Cell G6 PST. The cell D6 has the subtotal pre

Essentially what I would like, is if the report writer puts a G in the drop down. Cell F6 would show the gst amount of 5%. If writer puts in P then cell G6 calculates 7% pst. If writer puts in GP then it would put both calculations in both cells.

 

[kevin9999]

Do you mean something like this:

Book1
	D	E	F	G
5
6	$100.00	G	$5.00
7
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(OR(E6="G",E6="GP"),D6*0.05,"")
G6	G6	=IF(OR(E6="P",E6="GP"),D6*0.07,"")

Cells with Data Validation
Cell	Allow	Criteria
E6	List	G,P,GP

Book1
	D	E	F	G
5
6	$100.00	P		$7.00
7
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(OR(E6="G",E6="GP"),D6*0.05,"")
G6	G6	=IF(OR(E6="P",E6="GP"),D6*0.07,"")

Cells with Data Validation
Cell	Allow	Criteria
E6	List	G,P,GP

Book1
	D	E	F	G
5
6	$100.00	GP	$5.00	$7.00
7
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(OR(E6="G",E6="GP"),D6*0.05,"")
G6	G6	=IF(OR(E6="P",E6="GP"),D6*0.07,"")

Cells with Data Validation
Cell	Allow	Criteria
E6	List	G,P,GP

 

[Skrej]

I read your request as asking to simply display the static values of 5% or 7% in the corresponding cells based on E6. If that's the case, then try this:

F6 =IF(OR(E6="g",F6="GP"),"5%","")
G6=IF(OR(E6="p",F6="GP"),"7%","")

However, if I misunderstood and you are wanting a calculation of 5% or 7% of some value in another cell, then go with Kevin's solution.

 

[Johnathan_Jeremy]

Thank you! Both solved it nicely! I cant thank you enough!

 

[kevin9999]

Glad we could help & welcome to the Forum

 

[Johnathan_Jeremy]

Do you mean something like this:

Book1
	D	E	F	G
5
6	$100.00	G	$5.00
7
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(OR(E6="G",E6="GP"),D6*0.05,"")
G6	G6	=IF(OR(E6="P",E6="GP"),D6*0.07,"")

Cells with Data Validation
Cell	Allow	Criteria
E6	List	G,P,GP

Book1
	D	E	F	G
5
6	$100.00	P		$7.00
7
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(OR(E6="G",E6="GP"),D6*0.05,"")
G6	G6	=IF(OR(E6="P",E6="GP"),D6*0.07,"")

Cells with Data Validation
Cell	Allow	Criteria
E6	List	G,P,GP

Book1
	D	E	F	G
5
6	$100.00	GP	$5.00	$7.00
7
Sheet1

Cell Formulas
Range	Formula
F6	F6	=IF(OR(E6="G",E6="GP"),D6*0.05,"")
G6	G6	=IF(OR(E6="P",E6="GP"),D6*0.07,"")

Cells with Data Validation
Cell	Allow	Criteria
E6	List	G,P,GP

is there a way to make the cell with PST that reads like this =IF(OR(E9="P",E9="GP"),D9*0.07,"") to have it say 0.00 if there is no P or GP value? On my total I am having trouble with the Total when I add the cells from Subtotal, GST & PST. When there is only GST on an item the PST cell is blank and then the Total column has #VALUE in it.

 

[kevin9999]

Try changing it to this:
=IF(OR(E9="P",E9="GP"),D9*0.07,0)

 

[Johnathan_Jeremy]

Try changing it to this:
=IF(OR(E9="P",E9="GP"),D9*0.07,0)

Awesome! I wished I knew about this place earlier. Thanks a bunch! Happy Holidays