Excel Function

[Nasr Mostafa]

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/tutorial...2-cd97ef257c99/run-the-command-or-applic.aspx

 

[Ron Rosenfeld]

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/tutorial...2-cd97ef257c99/run-the-command-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