Formula to ensure a percentage is correct

LambertyE

New Member
Joined
Nov 19, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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 AmountAmount DiscountedDiscount Applied
$ 1,702,349.66$ 204,281.96Yes - 12% Discount
$ 1,065,846.67$ 127,901.60Yes - 12% Discount

With Formula:
=IF(H13/G13-1,"yes","no")
Total AmountAmount DiscountedDiscount Applied
$ 1,702,349.66$ 204,281.96yes
$ 1,065,846.67$ 127,901.60yes


Using the same formula:
=IF(H13/G13-1,"yes","no")
Total AmountAmount DiscountedDiscount Applied
$ 1,702,349.66$ 204,281.96yes
$ 1,065,846.67$ 204,281.96yes

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!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
20,049
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 19, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 1, 2002
Messages
56,649
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 19, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 19, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Nov 19, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,539
Messages
5,625,404
Members
416,100
Latest member
lirongr1996

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?

Disable AdBlock

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
Top