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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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