Nested formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2158689%22%20slang%3D%22en-US%22%3ENested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158689%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20My%20name%20is%20Oromokoma%20Charles.%20I%20am%20trying%20to%20use%20the%20nested%26nbsp%3B%3CSTRONG%3E%3Dif(and()%26nbsp%3B%3C%2FSTRONG%3Eto%20write%20an%20expression%20that%20grades%20students%20according%20to%20a%20number%20of%20cells%20for%20instance%20students'%20scores%20are%20entered%20in%20cells%20B1%3AE1.%3C%2FP%3E%3CP%3EAm%20grading%20them%20using%20cell%20F1%20which%20has%20the%20sum%20of%20B1%3AE1%20but%20for%20a%20student%20to%20get%20grade%20A%2C%20F1%20must%20be%20(F1%26lt%3B%3D12)%20and%20also%20B1%2CC1%2CD1%20and%20E1%20must%20not%20have%20a%209%20in%20any%20of%20them%20(B1%2CC1%2CD1%2CE1%26lt%3B9%20each).%20For%20a%20student%20to%20get%20grade%20B%2C%20F1%20must%20be%20F1%26lt%3B%3D24%20with%20the%20same%20conditions%20of%20B1%2CC1%2CD1%20and%20E1%20above.%20For%20a%20student%20to%20get%20grade%20C%2C%20F1must%20be%20(F1%26lt%3B%3D28)%20and%20the%20conditions%20of%20B1to%20E1%20must%20be%20true%20still%20and%20finally%20for%20a%20student%20to%20get%20grade%20D%2C%20F1%26lt%3B%3D32%20with%20or%20without%20meeting%20the%20conditions%20of%20B1%20to%20E1.%20Any%20student%20with%20F1%26gt%3B28%20gets%20ungraded.%20A%20student%20who%20meets%20the%20conditions%20of%20F1%20but%20violates%20the%20conditions%20of%20B1%20to%20E1%20gets%20the%20next%20grade.%20So%20if%20these%20students%20did%20the%20exam%20set%20and%20scored%20as%20follows.%20I%20want%20their%20results%20sheet%20to%20look%20this%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB%26nbsp%3B%20%26nbsp%3BC%26nbsp%3B%20%26nbsp%3BD%26nbsp%3B%20%26nbsp%3BE%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BF%3C%2FP%3E%3CP%3EStudent%20P%26nbsp%3B%20%26nbsp%3B%201%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%201%26nbsp%3B%20%26nbsp%3B%204%20grade1%3C%2FP%3E%3CP%3Estudent%20Q%26nbsp%3B%20%26nbsp%3B%202%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%208%26nbsp%3B%20%26nbsp%3B12%20grade%201%3C%2FP%3E%3CP%3Estudent%20R%26nbsp%3B%20%26nbsp%3B%201%26nbsp%3B%20%26nbsp%3B%201%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%209%26nbsp%3B%20%26nbsp%3B12%20grade%202%3C%2FP%3E%3CP%3Estudent%20S%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B4%26nbsp%3B%20%26nbsp%3B%202%26nbsp%3B%20%26nbsp%3B5%26nbsp%3B%20%26nbsp%3B9%26nbsp%3B%20%26nbsp%3B%2020%20grade%203%3C%2FP%3E%3CP%3Estudent%20T%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B6%26nbsp%3B%20%26nbsp%3B%206%26nbsp%3B%20%26nbsp%3B6%26nbsp%3B%20%26nbsp%3B6%26nbsp%3B%20%26nbsp%3B%2024%20grade%202%3C%2FP%3E%3CP%3EHow%20do%20I%20write%20a%20nest%20%3Dif(and()that%20can%20correctly%20outputs%20that%3F%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2158689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2158762%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F975772%22%20target%3D%22_blank%22%3E%40Oromokoma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EShouldn't%20Student%20R%20get%20a%20C%20(grade%203)%20since%20E3%3D9%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20G1%20as%20an%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(F1%26lt%3B%3D12%2CB1%3AE1%26lt%3B9)%2C%22A%22%2CIF(AND(F1%26lt%3B%3D24%2CB1%3AE1%26lt%3B9)%2C%22B%22%2CIF(AND(F1%26lt%3B%3D28%2CB1%3AE1%26lt%3B9)%2C%22C%22%2CIF(F1%26lt%3B%3D32%2C%22D%22%2C%22-%22))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello! My name is Oromokoma Charles. I am trying to use the nested =if(and() to write an expression that grades students according to a number of cells for instance students' scores are entered in cells B1:E1.

Am grading them using cell F1 which has the sum of B1:E1 but for a student to get grade A, F1 must be (F1<=12) and also B1,C1,D1 and E1 must not have a 9 in any of them (B1,C1,D1,E1<9 each). For a student to get grade B, F1 must be F1<=24 with the same conditions of B1,C1,D1 and E1 above. For a student to get grade C, F1must be (F1<=28) and the conditions of B1to E1 must be true still and finally for a student to get grade D, F1<=32 with or without meeting the conditions of B1 to E1. Any student with F1>28 gets ungraded. A student who meets the conditions of F1 but violates the conditions of B1 to E1 gets the next grade. So if these students did the exam set and scored as follows. I want their results sheet to look this this:

                   B   C   D   E     F

Student P    1   1   1    1    4 grade1

student Q    2   1   1    8   12 grade 1

student R    1    1   1    9   12 grade 2

student S     4    2   5   9    20 grade 3

student T     6    6   6   6    24 grade 2

How do I write a nest =if(and()that can correctly outputs that?

Thanks.

1 Reply

@Oromokoma 

Shouldn't Student R get a C (grade 3) since E3=9?

 

In G1 as an array formula confirmed with Ctrl+Shift+Enter:

 

=IF(AND(F1<=12,B1:E1<9),"A",IF(AND(F1<=24,B1:E1<9),"B",IF(AND(F1<=28,B1:E1<9),"C",IF(F1<=32,"D","-"))))

 

Fill down.