Prev: Pivot Table- Column 2 sort
Next: Data Connection between Excel 2007 and SharePoint 2007
From: Reno on 20 Jan 2010 16:29
want to assign a letter or number based on calculated value (much like a
grading scale.) If calc value is:
..0 to .25= I
..26 to .50= H
..
..
..
final one >5.1 =A

can you do as an array beside doing the If > and < formula
thx
From: Gary''s Student on 20 Jan 2010 17:06
Use the =VLOOKUP() function
--
Gary''s Student - gsnu200909


"Reno" wrote:

> want to assign a letter or number based on calculated value (much like a
> grading scale.) If calc value is:
> .0 to .25= I
> .26 to .50= H
> .
> .
> .
> final one >5.1 =A
>
> can you do as an array beside doing the If > and < formula
> thx
From: Jim Thomlinson on 20 Jan 2010 17:12
You can use index match as follows...
=IF(D2>A21, C21, INDEX(C1:C21, MATCH(D2, A1:A21,1)))

Where your data looks like this and your formula is in D2
A B C D
0 0.25 A T
0.26 0.5 B 4.95
0.51 0.75 C
0.76 1 D
1.01 1.25 E
1.26 1.5 F
1.51 1.75 G
1.76 2 H
2.01 2.25 I
2.26 2.5 J
2.51 2.75 K
2.76 3 L
3.01 3.25 M
3.26 3.5 N
3.51 3.75 O
3.76 4 P
4.01 4.25 Q
4.26 4.5 R
4.51 4.75 S
4.76 5 T
5.01 U

--
HTH...

Jim Thomlinson


"Reno" wrote:

> want to assign a letter or number based on calculated value (much like a
> grading scale.) If calc value is:
> .0 to .25= I
> .26 to .50= H
> .
> .
> .
> final one >5.1 =A
>
> can you do as an array beside doing the If > and < formula
> thx
From: Jim Thomlinson on 20 Jan 2010 17:14
Sorry Formula is in D1 and data entry to look up is in D2.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> You can use index match as follows...
> =IF(D2>A21, C21, INDEX(C1:C21, MATCH(D2, A1:A21,1)))
>
> Where your data looks like this and your formula is in D2
> A B C D
> 0 0.25 A T
> 0.26 0.5 B 4.95
> 0.51 0.75 C
> 0.76 1 D
> 1.01 1.25 E
> 1.26 1.5 F
> 1.51 1.75 G
> 1.76 2 H
> 2.01 2.25 I
> 2.26 2.5 J
> 2.51 2.75 K
> 2.76 3 L
> 3.01 3.25 M
> 3.26 3.5 N
> 3.51 3.75 O
> 3.76 4 P
> 4.01 4.25 Q
> 4.26 4.5 R
> 4.51 4.75 S
> 4.76 5 T
> 5.01 U
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Reno" wrote:
>
> > want to assign a letter or number based on calculated value (much like a
> > grading scale.) If calc value is:
> > .0 to .25= I
> > .26 to .50= H
> > .
> > .
> > .
> > final one >5.1 =A
> >
> > can you do as an array beside doing the If > and < formula
> > thx
From: Lars-�ke Aspelin on 20 Jan 2010 17:20
On Wed, 20 Jan 2010 13:29:01 -0800, Reno
<Reno(a)discussions.microsoft.com> wrote:

>want to assign a letter or number based on calculated value (much like a
>grading scale.) If calc value is:
>.0 to .25= I
>.26 to .50= H
>.
>.
>.
>final one >5.1 =A
>
>can you do as an array beside doing the If > and < formula
>thx


If your calculated value is in cell A1, try this formula in the cell
wher you want your "grade":

=LOOKUP(A1,{0,0.26,0.51,1.01,1.51,2.01,3.01,4.01,5.11},{"I","H","G","F","E","D","C","B","A"})

Change the values to get the correct limits between the different
grades.

Hope this helps / Lars-�ke


 |  Next  |  Last
Pages: 1 2
Prev: Pivot Table- Column 2 sort
Next: Data Connection between Excel 2007 and SharePoint 2007