if statement for +/- a percentage

marshy3300

Board Regular
Joined
Aug 17, 2014
Messages
59
Hello, I'm trying to display a tick or cross in F if a variance (D-C) is less than or greater than C but i'm having a total mind blank. Should be easy but its not!
I've got the wingdings side easily just the formula is stuffing me around

So

Num1 ...Num2 ...Variance.....Mark
C.............D............E...........F
600 ......600............0........Tick
13 ........14 ............1........Cross
312 ......308 ..........-4 ........Tick
312 ......388 ..........86 .......Cross
312 ......333...........21.........Tick
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
86 (though my math sucks and should be 76) is greater than 10% of 312 so its a cross
21 is within the +/- 10% mark

Really bad day..... This is all based off +/- 10% variance from C
 
Last edited:
Upvote 0
So this

Code:
=IF(ABS(E1)< ABS(C1*0.1),CHAR(252),CHAR(251))
 
Last edited:
Upvote 0
Thanks Michael,

Looks like that only works if the variance is 0. Otherwise all the examples show wrong though the symbol does change over the course of the sheet
*
Amended - Wrong cell references... Works a treat
Thanks heaps
 
Last edited:
Upvote 0

Forum statistics

Threads
1,215,054
Messages
6,122,893
Members
449,097
Latest member
dbomb1414

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
Back
Top