# Formula to ensure a percentage is correct

#### LambertyE

##### New Member
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!

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Joe4

##### MrExcel MVP, Junior Admin
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

##### Well-known Member
I think rounding might be in play here....note the actual percentages
Book1
GHIJK
2Total AmountAmount DiscountedDiscount AppliedDifference
3\$1,702,349.66 \$204,281.96 no12.00000004699%
4\$1,065,846.67 \$204,281.96 no19.16616768151%
Sheet1
Cell Formulas
RangeFormula
J3J3=IF(H3/G3=0.12,"yes","no")
K3:K4K3=H3/G3
J4J4=IF(H4/G4-1=12%,"yes","no")

#### Dave Patton

##### Well-known Member
T202012a.xlsm
GHI
131,702,349.66204,281.96yes
1b
Cell Formulas
RangeFormula
I13I13=IF(ROUND(H13/G13,2)=0.12,"yes","no")

#### LambertyE

##### New Member

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
GHI
131,702,349.66204,281.96yes
1b
Cell Formulas
RangeFormula
I13I13=IF(ROUND(H13/G13,2)=0.12,"yes","no")
Thank you so much! You nailed, formula worked.

#### Joe4

##### MrExcel MVP, Junior Admin
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

##### New Member

T202012a.xlsm
GHI
131,702,349.66204,281.96yes
1b
Cell Formulas
RangeFormula
I13I13=IF(ROUND(H13/G13,2)=0.12,"yes","no")
Thank you! This formula works perfectly!

#### Dave Patton

##### Well-known Member
Thanks for the feedback.

along the same idea but a bit shorter

T202012a.xlsm
GHI
1012%
11Discount
12Applied
131,065,846.67127,901.60TRUE
141,065,846.67127,901.60TRUE
15
1b
Cell Formulas
RangeFormula
I13I13=ROUND(H13/G13,2)=\$I\$10
I14I14=ROUND(G14*\$I\$10,2)=H14

#### LambertyE

##### New Member
Thanks for the feedback.

along the same idea but a bit shorter

T202012a.xlsm
GHI
1012%
11Discount
12Applied
131,065,846.67127,901.60TRUE
141,065,846.67127,901.60TRUE
15
1b
Cell Formulas
RangeFormula
I13I13=ROUND(H13/G13,2)=\$I\$10
I14I14=ROUND(G14*\$I\$10,2)=H14
Oh wow! thank you so much! Greatly appreciated!

#### LambertyE

##### New Member
Thanks for the feedback.

along the same idea but a bit shorter

T202012a.xlsm
GHI
1012%
11Discount
12Applied
131,065,846.67127,901.60TRUE
141,065,846.67127,901.60TRUE
15
1b
Cell Formulas
RangeFormula
I13I13=ROUND(H13/G13,2)=\$I\$10
I14I14=ROUND(G14*\$I\$10,2)=H14
Oh wow! thank you so much! Greatly appreciated!

Replies
1
Views
88
Replies
8
Views
83
Replies
3
Views
123
Replies
5
Views
163
Replies
6
Views
253

1,126,893
Messages
5,621,484
Members
415,844
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back