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

shiva786

New Member
Joined
Feb 5, 2014
Messages
29
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.
 
This formula should do it:
=MAX(INT(A1/0.25)-1,0)
 
Upvote 0
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!!!
 
Upvote 0
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:
Code:
=IF(A1=1,3,IF(A1>=0.75,2,IF(A1>=0.5,1,0)))
 
Upvote 0

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