Conditional Score formula for Excel work sheet?("IF" formula for my excel sheet, how to use?)

[shiva786]

Dear Friends/Sir/Madam,
I have checklist of 5 pionts, in that for each piont minimum score is "0" and maximum score is "3".

if below 50% = "0" score should appear in cell(Score Column)
if 50% to 75% comes than score will be = "1"
if above 75% to 99% comes than score will be = "2"
if "100%" comes than score will be three = "3"

Now i request you people to plz help me out with some formula or VBA,,,!!to get solved above problem. thank you so much for u r time and consideration.

 

[Joe4]

This formula should do it:
=MAX(INT(A1/0.25)-1,0)

 

[shiva786]

mind blowing solution my dear friend....plz plz can u explain me how to read this formul, means i want to understand this formula..plz brother teach me if u are free!!!

 

[Joe4]

Thank you.

OK, so we know that a percentage is really just a decimal between 0 and 1.
In looking at your conditions, it looks like the value jumps up by 1 for every 25% (or .25).

So, what INT(A1/.25) does is the following:
It divides the value in A1 by .25, and returns just the integer (whole number) of that calculation (so it drops the fractional part).

So (assuming two decimal places in this instance),
between 0 - .24, it will return 0,
between .25 - .49, it will return 1,
between .5 -.74, it will return 2,
between .75 - .99, it will return 3,
at 1, it will return 4.

That is one higher than we need, so we subtract one. However, for the first bracket (0 - .24), that would then return -1 instead of 0. So we use the MAX function, which says to take the maximum of the two values listed. Since I have 0 as the other option, the maximum between 0 and -1 is 0. So applying that maximum function ensures that the value returned will never be less than zero.

Alternatively, you could use a Nested IF formula, which may be easier to understand, but it is a little longer (and not nearly as cool!).

That formula would look like this:

=IF(A1=1,3,IF(A1>=0.75,2,IF(A1>=0.5,1,0)))