Formula to ensure a percentage is correct

[LambertyE]

Hi all,

I need help! I am looking for a formula to know if "yes" or "no" a percentage between two columns equals 12% in excel 2016. This should be simple but for some reason the ones I have created failed.

For example:
This is manually done:

Total Amount	Amount Discounted	Discount Applied
$ 1,702,349.66	$ 204,281.96	Yes - 12% Discount
$ 1,065,846.67	$ 127,901.60	Yes - 12% Discount

With Formula:
=IF(H13/G13-1,"yes","no")

Total Amount	Amount Discounted	Discount Applied
$ 1,702,349.66	$ 204,281.96	yes
$ 1,065,846.67	$ 127,901.60	yes

Using the same formula:
=IF(H13/G13-1,"yes","no")

Total Amount	Amount Discounted	Discount Applied
$ 1,702,349.66	$ 204,281.96	yes
$ 1,065,846.67	$ 204,281.96	yes

The answer under the "Discount Applied" column should say "no" instead.

I have also tried:
=IF(AND(H13/G13>=12%,H13/G13<=12%),"Yes","No")
=IF(AVERAGE(G13:H13)=12,"yes","no")
=IF(AVERAGE(G13:H13)>=12,IF(AVERAGE(G13:H13)<=12,"yes","no"))
=IF(H13/G13-1,"YES","NO")

It might be fairly simple but for some reason I cannot get to it. Can someone please shed some light?

Thanks!

 

[Joe4]

What exactly are you trying to do with this part of your formula?
H13/G13-1

Should you be checking if that is equal to something?
At the very least, you may need to use the round function to account for tiny decimals, i.e
ROUND(H13/G13,2)

 

[Michael M]

I think rounding might be in play here....note the actual percentages

Book1
	G	H	I	J	K
2	Total Amount	Amount Discounted		Discount Applied	Difference
3	$1,702,349.66	$204,281.96		no	12.00000004699%
4	$1,065,846.67	$204,281.96		no	19.16616768151%
Sheet1

Cell Formulas
Range	Formula
J3	J3	=IF(H3/G3=0.12,"yes","no")
K3:K4	K3	=H3/G3
J4	J4	=IF(H4/G4-1=12%,"yes","no")

 

[Dave Patton]

T202012a.xlsm
	G	H	I
13	1,702,349.66	204,281.96	yes
1b

Cell Formulas
Range	Formula
I13	I13	=IF(ROUND(H13/G13,2)=0.12,"yes","no")

 

[LambertyE]

What exactly are you trying to do with this part of your formula?
H13/G13-1

Should you be checking if that is equal to something?
At the very least, you may need to use the round function to account for tiny decimals, i.e
ROUND(H13/G13,2)

Yes, I was trying to get to the 12% that I need as a result.

T202012a.xlsm
	G	H	I
13	1,702,349.66	204,281.96	yes
1b

Cell Formulas
Range	Formula
I13	I13	=IF(ROUND(H13/G13,2)=0.12,"yes","no")

Thank you so much! You nailed, formula worked.

 

[Joe4]

Yes, I was trying to get to the 12% that I need as a result.

Why wasn't that part of that formula then (the 12%)?
Michael and Dave's posts show you the formulas with that 12% being factored in.

 

[LambertyE]

T202012a.xlsm
	G	H	I
13	1,702,349.66	204,281.96	yes
1b

Cell Formulas
Range	Formula
I13	I13	=IF(ROUND(H13/G13,2)=0.12,"yes","no")

Thank you! This formula works perfectly!

 

[Dave Patton]

Thanks for the feedback.

along the same idea but a bit shorter

T202012a.xlsm
	G	H	I
10			12%
11			Discount
12			Applied
13	1,065,846.67	127,901.60	TRUE
14	1,065,846.67	127,901.60	TRUE
15
1b

Cell Formulas
Range	Formula
I13	I13	=ROUND(H13/G13,2)=$I$10
I14	I14	=ROUND(G14*$I$10,2)=H14

 

[LambertyE]

Thanks for the feedback.

along the same idea but a bit shorter

T202012a.xlsm
	G	H	I
10			12%
11			Discount
12			Applied
13	1,065,846.67	127,901.60	TRUE
14	1,065,846.67	127,901.60	TRUE
15
1b

Cell Formulas
Range	Formula
I13	I13	=ROUND(H13/G13,2)=$I$10
I14	I14	=ROUND(G14*$I$10,2)=H14

Oh wow! thank you so much! Greatly appreciated!

 

[LambertyE]

Thanks for the feedback.

along the same idea but a bit shorter

T202012a.xlsm
	G	H	I
10			12%
11			Discount
12			Applied
13	1,065,846.67	127,901.60	TRUE
14	1,065,846.67	127,901.60	TRUE
15
1b

Cell Formulas
Range	Formula
I13	I13	=ROUND(H13/G13,2)=$I$10
I14	I14	=ROUND(G14*$I$10,2)=H14

Oh wow! thank you so much! Greatly appreciated!