Help with IF statement and negative numbers

Grammarjunkie

Board Regular
Joined
Mar 22, 2016
Messages
86
I'm not an Excel novice, but my brain is just not seeing how to appropriately do this. I'm hoping maybe there's a function I've never used before, so I'm not aware of it. Or maybe I'm simply too close to the problem and not thinking outside of the box. But either way, my issue is follows:

In B19 I have a percentage, in D19 I have a percentage. In F19 I've subtracted D19 from B19 and condition formatted so that if the cell value is between -3.00% and 3.00%, it's green. Anything outside of that range is red. (i.e. - the difference between B19 and D19 can only have a 3% variance)

In cell H19, I need an IF statement to populate "APR within tolerance" if it's IN the 3% variance, "APR not within tolerance" if it's outside of that 3% variance.

The caveat being I can make anything over 3% make the cell read "APR within tolerance" effectively enough, but then anything less than 3% is "not within tolerance"... which isn't true. Anything to -3% IS "within tolerance."

Essentially, I need like the "Between" condition formatting, but formula form.

I hope this made sense. For the sake of clarity, this is what I currently have:

=IF(F19="","",IF(F19>3%,"APR not within tolerance", IF(F19<(3),"APR not within tolerance","APR within tolerance")))

I've also tried F19>(3). (Since I'm not sure if Excel counts larger negative numbers as less than or greater than. Neither worked.)

I hope I make sense, and I appreciate any advice.
Also, I apologize if this is a dumb question. I have no idea why I can't figure this out. haha.
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Grammarjunkie

Board Regular
Joined
Mar 22, 2016
Messages
86
Totally just figured it out. Lol.

I was reading my post and it hit me:

=IF(F19="","",IF(OR(F19>3%,F19<-3%),"APR not within tolerance","APR within tolerance"))

I'm not sure why it wasn't obvious before.

I guess I was overthinking and needed a break.

Thanks for stopping by though!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

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