Thread: advanced IF statement

hello,

i was wondering if anyone could tell me how to create an IF statement where i want to compare a a set criteria to a result and give it 1 mark it it makes the criteria and then and extra 1 point for ever 5% it is above the criteria

thanks

please any one help me Iam egyptian and I am interseted in learning and use excel and access so please can anyone contact me at this E-mail:
waffaa_qm@yahoo.com
I need a project by excel which can put a yellow marker on the cell which is repeated in another column
and send with it the explaination
Iwill very greatfull if anyone reply
thanks

try something like:

IF (value<criteria,0,INT(20*(value-criteria)/criteria))

ie use Maths rather than a complex IF.

5% of crtieria is 5*criteria/100 or criteria/20

as I understand your problem you want to know how many times this 5% is value larger than criteria thus

(value-critera)/(criteria/20) or 20*(value-criteria)/criteria

take the INT (or perhaps ROUND) to get the required answer.