IF statement

[andycreber]

Hi,
Please could someone help me with an IF statement. I would like to write the below in an IF statement if possible.

scenario:


Total number of items = 39

For each item that is failed, 0.01 point deducted per 0.01% that the item is below 99.5% but above or equal to 96% and

For each item that is failed, 0.02 point deducted per 0.02% that the item is below 96% but above or equal to 90% and

For each item that is failed, 15.5 point deducted that the item is below 90%


Happy to have a table using each score and point deduction similar to below if easier for combining Vlookup and likewise using more cells of table data to get the result.
[TABLE="class: cms_table, width: 246"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TD]Score[/TD]
[TD]PP deduction[/TD]
[/TR]
[TR]
[TD="align: right"]90.0%[/TD]
[TD="align: right"]15.5[/TD]
[/TR]
[TR]
[TD="align: right"]90.0%[/TD]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]95.9%[/TD]
[TD="align: right"]0.02[/TD]
[/TR]
[TR]
[TD="align: right"]96.0%[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]99.4%[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance, please let me know if you need further clarification.​

 

[RasGhul]

Re: IF statement Help

Try

=IF(AND(A1<0.995,>=0.96),0.01,IF(AND(A1<0.96,A1>=0.9),0.02,IF(A1<=0.9,15.5,"")))

 

[andycreber]

Re: IF statement Help

Try

=IF(AND(A1<0.995,>=0.96),0.01,IF(AND(A1<0.96,A1>=0.9),0.02,IF(A1<=0.9,15.5,"")))

Hi,

That works perfectly, thank you

 

[RasGhul]

Re: IF statement Help

you're welcome

 

[andycreber]

Re: IF statement Help

HI RasGhul,

I have another one for you...

using the same data as the first question , in the same formula I now want to multiply for every 0.01% difference multiply by 0.01 when below 99.5% and above equal to 96% and

multiply for every 0.01% difference multiply by 0.02 when below 96% and above equal to 90% and
apply 15.5 when below 90%​

is that possible?
many thanks

 

[RasGhul]

Re: IF statement Help

Ok so we have the hurdles correct, what do you the calculation to be for each hurdle?

ie

[COLOR=#333333][I]=IF(AND(A1<0.995,>=0.96),sum(0.01*##)?[/I][/COLOR]

Can you give me what the end result should be so I get the formula correct.