logic (IF) functions

elaine · 06-22-2006, 01:50 PM

I have the following function that I am using to replace letter grades with
grade points. Excel will not allow me to enter more than seven nested
functions. Is there another way?
=IF(D3="a","4",IF(D3="b+","3.5",
IF(D3="b","3",IF(D3="c+","2.5",IF(D3="C","2",IF(D3="D+","1.5",IF(D3="D","1",IF(D3="F","0"))))))))

Franz Verga · 06-22-2006, 01:55 PM

Nel post news:09507D31-D366-4E65-B476-84A27A2CE020@microsoft.com
*elaine* ha scritto:

> I have the following function that I am using to replace letter
> grades with grade points. Excel will not allow me to enter more than
> seven nested functions. Is there another way?
> =IF(D3="a","4",IF(D3="b+","3.5",
> IF(D3="b","3",IF(D3="c+","2.5",IF(D3="C","2",IF(D3="D+","1.5",IF(D3="D","1",IF(D3="F","0"))))))))

Till Excel 2003 there's a limit of 7 level of nested functions.
You can solve using a table and the VLOOKUP function.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Nick Hodge · 06-22-2006, 03:10 PM

Franz

Petty, but it's until XL2007, in case anyone tries more in XL2003

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

"Franz Verga" <fra68ve@InVento.it> wrote in message
news:uc3zcRilGHA.1204@TK2MSFTNGP02.phx.gbl...
> Nel post news:09507D31-D366-4E65-B476-84A27A2CE020@microsoft.com
> *elaine* ha scritto:
>
>> I have the following function that I am using to replace letter
>> grades with grade points. Excel will not allow me to enter more than
>> seven nested functions. Is there another way?
>> =IF(D3="a","4",IF(D3="b+","3.5",
>> IF(D3="b","3",IF(D3="c+","2.5",IF(D3="C","2",IF(D3="D+","1.5",IF(D3="D","1",IF(D3="F","0"))))))))
>
> Till Excel 2003 there's a limit of 7 level of nested functions.
> You can solve using a table and the VLOOKUP function.
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>

**Bearacade** · 06-22-2006, 02:01 PM

Or if you insist of doing a long if statement, try to following:

=IF(LEFT(E9,1)="a",4,IF(LEFT(E9,1)="b",3,IF(LEFT(E9,1)="c",2,IF(LEFT(E9,1)="d",1,IF(LEFT(E9,1)="f","0","OUT OF RANGE"))))) & IF(RIGHT(E9,1)="+",".5","")

HTH

Ragdyer · 06-22-2006, 09:35 PM

You could try this:

=LOOKUP(D3,{"A","B","B+","C","C+","D","D+","F";4,3,3.5,2,2.5,1,1.5,0})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"elaine" <elaine@discussions.microsoft.com> wrote in message
news:09507D31-D366-4E65-B476-84A27A2CE020@microsoft.com...
>I have the following function that I am using to replace letter grades with
> grade points. Excel will not allow me to enter more than seven nested
> functions. Is there another way?
> =IF(D3="a","4",IF(D3="b+","3.5",
> IF(D3="b","3",IF(D3="c+","2.5",IF(D3="C","2",IF(D3="D+","1.5",IF(D3="D","1",IF(D3="F","0"))))))))