LAMBDA EXCEL: Letter Grade

BY DAVID HAGER In a continuing series of posts on showing how to convert (long, but not necessarily long) Excel formulas into lambda functions, here is an useful formula I created for use in applying letter grades to numeric scores. #Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager | Excel For You (wordpress.com) This formula is shown below. The “letter buckets” that correspond to each grade might not fit all situations. =IF(A5>=90,”A”,IF(A5>=80,”B”,IF(A5>=70,”C”,IF(A5>=60,”D”,”F”))))&IF(A5>98,”+”,IF(A5<59,””,IF(OR(RIGHT(A5,1)={“1″,”2″,”0″}),”-“,IF(OR(RIGHT(A5,1)={“8″,”9″}),”+”,””)))) In the Name Manager, assign a new name of LetterGrade. In the Refers To box, place the lambda form of the letter grade formula: =LAMBDA(grade,IF(grade>=90,”A”,IF(grade>=80,”B”,IF(grade>=70,”C”,IF(grade>=60,”D”,”F”))))&IF(grade>98,”+”,IF(grade<59,””,IF(OR(RIGHT(grade,1)={“1″,”2″,”0″}),”-“,IF(OR(RIGHT(grade,1)={“8″,”9″}),”+”,””))))) Then, if =LetterGrade(88) was placed in a cell, the result would be “B+”. Currently, only those users of Excel 365 that are in the Office Insiders beta program have access to the LAMBDA function. However, it is not too early to start collecting your favorite converted formulas so you will be ready to use them when it is available to you.

LAMBDA EXCEL: Bible Verse

BY DAVID HAGER

One of my first blog posts about the use of Excel’s web functions was about a method to return a specified Bible verse.

Lookup a Bible Verse Using #Excel (w/o VBA) by David Hager – Excel For You (wordpress.com)

It used several lookup techniques involving Excel data validation lists from worksheet cells to allow the user to select the desired verses. However, I decided to use retrieving a Bible verse using the new LAMBDA conversion, which required using a different way to get the information directly from the Bible verse. Thus, I needed to create some string manipulation formulas to use in the master formula. The new methodology can be seen in the formula below.

If BVerse = 1 Samuel 2:13 then

=FILTERXML(WEBSERVICE(“http://labs.bible.org/api/?passage=”&amp;

LEFT(BVerse,LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))-1)&” “&

MID(BVerse,LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))+1,FIND(“:”,BVerse)-LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))-1)&”:”&MID(BVerse,FIND(“:”,BVerse)+1,255)

&”&type=xml”),”//text”)

where the book of the bible verse is

=LEFT(BVerse,LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))-1)

and the chapter of the bible verse is

=MID(BVerse,LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))+1,FIND(“:”,BVerse)-LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))-1)

and the verse number is

=MID(BVerse,FIND(“:”,BVerse)+1,255)

So, then 1 Samuel is the book, 2 is the chapter and 13 is the verse number.

To convert this into a LAMBDA formula, in the Name Manager the new name should be BibleVerse and in the Refers To box should be written:

=LAMBDA(BVerse,FILTERXML(WEBSERVICE(“http://labs.bible.org/api/?passage=”&LEFT(BVerse,LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))-1)&” “&MID(BVerse,LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))+1,FIND(“:”,BVerse)-LOOKUP(2^15,FIND(” “,BVerse,SEQUENCE(LEN(BVerse))))-1)&”:”&MID(BVerse,FIND(“:”,BVerse)+1,255)&”&type=xml”),”//text”))

(Based on the naming convention used in the xml source, if the first part of the book name has a number like 1st, 2nd or 3rd, the bible verse string should always be written with a 1, 2, or 3.)

Thus, again for 1 Samuel 2:13 in A1, if =BibleVerse(A1) is typed in B1, the return value is:

“This was the priests’ routine with the people. Whenever anyone was making a sacrifice, the priest’s attendant would come with a three-pronged fork in his hand, just as the meat was boiling.”

An Excel file containing the described LAMBDA function can be downloaded by clicking the following link.:

lamda_bibleverseDownload

BTW, if the Bible verse is written incorrectly, the IFERROR function can be used to encapsulate the LAMBDA formula to return a Not Found message if desired.

Stay tuned for more great LAMBDA Excel creations!