Conditional Value based on another cell

**jcowley** · 08-31-2010, 09:38 AM

Hi All,

I am making a scorecard, and if a cell is above 30%, I want the cell next to it to score 5 points, and if the cell is below 30%, I want it to score 2 points. Is this possible?

I have attached a spreadsheet to show you the layout.

Many thanks

**zbor** · 08-31-2010, 09:42 AM

Try this in E4:

=IF(D4>0.3, 5, 2)

**jcowley** · 08-31-2010, 10:13 AM

Worked perfectly thanks. I have realised that for one of them I need the following score;

If it is between 90-100% you get 5 points, but for above or below those figures you would get 2?

**zbor** · 08-31-2010, 10:19 AM

Try this in E4:

=IF(D4>0.9, 5, 2)

**jcowley** · 08-31-2010, 10:42 AM

Originally Posted by zbor

Try this in E4:

=IF(D4>0.9, 5, 2)

This works for below 90%, but I also need it to drop for when over 110%

EG

0-89% = 2 points
90-110% = 5 points
110%+ = 2 points

**zbor** · 08-31-2010, 02:22 PM

Try this:

=IF(ABS(1-D5)>0.1, 2, 5)

**jcowley** · 09-02-2010, 09:24 AM

Thanks again, worked perfectly. Hopefully this will be the last one. I now need to put in a 3 stage point scoring system.

0-75% = 1 point
75-95 = 0.5 points
>95% = 0 points

Would this work using a similar formula?

Many thanks