Excel Function
 

Hi What is the formula to get the following

Mark Points
90 & more 4
85 & less 90 3.7
82.5 & less 85 3.3
77.5 & less 82.5 3
75 & less 77.5 2.7
67.5 & less 75 2.3
67.5 & less 72.5 2
65 & lees 67.5 1.7
62.5 & less 65 1.3
60 & less 62.5 1
less 60 0
I mean when I put the mark in A1 cell the point appear in B1 cell
many thanks

EggHeadCafe - Software Developer Portal of Choice
Run the command or application in the Security context of specified user
http://www.eggheadcafe.com/tutorials...or-applic.aspx

Excel Function
 

On Sun, 08 Nov 2009 00:38:57 -0800, Nasr Mostafa wrote:

Hi What is the formula to get the following

Mark Points
90 & more 4
85 & less 90 3.7
82.5 & less 85 3.3
77.5 & less 82.5 3
75 & less 77.5 2.7
67.5 & less 75 2.3
67.5 & less 72.5 2
65 & lees 67.5 1.7
62.5 & less 65 1.3
60 & less 62.5 1
less 60 0
I mean when I put the mark in A1 cell the point appear in B1 cell
many thanks

EggHeadCafe - Software Developer Portal of Choice
Run the command or application in the Security context of specified user
http://www.eggheadcafe.com/tutorials...or-applic.aspx

You can use VLOOKUP.

Put your data in a range, with Mark sorted ascending. Something like:

Mark Points
0 0
60.0 1
62.5 1.3
65.0 1.7
67.5 2
67.5 2.3
75.0 2.7
77.5 3
82.5 3.3
85.0 3.7
90.0 4

Note that you need to enter a 0 Mark range.
*CORRECT* the overlapping Mark range (at 67.5)

Then use a formula like: =VLOOKUP(A1,table_range,2)
--ron