Selected Answer
Hi Nitil
You don't say what the criteria for +1 and -1 are but the IF function can be used here.
That takes the form =IF(<<test>>, <<value if test is logical TRUE>>, <<value if test is FALSE>>)
Your spreadsheet has a single value for "Range of Age" so I've assumed that is a maximum age and in E3 (for example), the formula is:
=IF(D3<=E3,1,-1)
Here, the test equates to "Is D3 less than or equal to E3?". If that's true, the cell shows the 1 but if not, it will display -1 (see cells below that).
If the "Range of Age" is a minimum, change the test to "D3>=E3".
IF can output text too- in the green cells G3 to G5 of my revised workbook (attached), I've changed the "less than" formula from E3 to show "Acceptable" or "Does not meet critieria" using this variant:
=IF(D3<=E3,"Acceptable","Does not meet criteria")
If however you really have a range age, IF can do that if you combine it with the AND function. The IF test is then AND(<<test 1>>, <<test 2>>) and will be TRUE only if both tests are met. In the orange cells G8 to G10 of my revised workbook (attached), I've tested against a minmium and maximum age (in E8 and F8 for example) and use this formula (where the test is in bold:
=IF(AND(D8>=E8, D8<=F8),"Acceptable","Reject")
and you could change the text for 1 and -1 if the value feeds into a scoring system.
In fact, if the two ages are always the same, you don't need to repeat them but could put them in E1 and F1 for example and make the test
AND(D8>=$E$1, D8<=$F$1)
where the $ sign fixes the cell references when you copy the formula down or elsewhere.
Hope this helps. Check out the Tutuorial section for help on IF and AND if you need to.
REVISION 1: Spreadsheet revised to show a formula where Acceptable means the age is no more than 1 year from a reference age (as per user clarification to the Question). This is shown in yellow cells E14 to E18 of the revised workbook attached and uses the ABS function since ABS(number) returns the value of a number without the + or - sign (and is applied to the subtraction of one cell value from another). This is used as the test for the IF (highlighted in bold below):
=IF(ABS(D14-E14)<=1,"Acceptable","Not acceptable")
