Home

Use IF statement in Excel with set of cells

%3CLINGO-SUB%20id%3D%22lingo-sub-486955%22%20slang%3D%22en-US%22%3EUse%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-486955%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20need%20to%20use%20the%20if%20statement%20with%20set%20of%20cells%2C%20say%20C4%3AC33%2C%20and%20the%20return%20for%20TRUE%20condition%20is%20the%20same%20as%20cell%20value%2C%20while%20for%20false%20condition%20the%20return%20value%20is%20zero.%20The%20following%20is%20an%20explanation%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMIN(IF(C4%3AC33%3CC40%3E%3C%2FC40%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20this%20form%20is%20giving%20invalid%20value%20because%20I%20am%20using%20the%20same%20set%20as%20a%20True%20result.%3C%2FP%3E%3CP%3EAnyone%20can%20give%20an%20idea%20how%20to%20do%20this%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-486955%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487096%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328396%22%20target%3D%22_blank%22%3E%40m81hassan_51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%3F%3C%2FP%3E%3CPRE%3E%3DMINIFS(C4%3AC33%2CC4%3AC33%2C%22%26lt%3B%22%26amp%3BC40)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487248%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487248%22%20slang%3D%22en-US%22%3EPerhaps%2C%20this%20is%20what%20you%20need%3A%3CBR%20%2F%3E%3D(MIN(C4%3AC%2433)%3CC%3E%3C%2FC%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487377%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487377%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20reply%2C%20What%20I%20exactly%20want%2C%20is%20that%2C%20if%20the%20value%20of%20specifc%20cell%20in%20column%20C%20is%20lower%20than%20the%20fixed%20value%20(placed%20in%20cell%20C40)%20the%20the%20output%20from%20If-statement%20is%20the%20same%20value%20of%20specific%20C-cell%2C%20else%2C%20the%20output%20value%20is%20zero.%20C40%20is%20only%20used%20for%20condition%2Fcomparesion%20and%20not%20used%20as%20an%20output%20neither%20in%20True%20nor%20in%20false%20conditions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487426%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487426%22%20slang%3D%22en-US%22%3EYou%20need%20no%20MIN.%20Thus%2C%20what%20you%20need%20is%3A%3CBR%20%2F%3E%3D(C4%3CC%3E%3C%2FC%3EThe%20above%20formula%20returns%20either%20C4%20or%200.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487452%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487452%22%20slang%3D%22en-US%22%3EWhat%20I%20need%20is%20to%20use%20a%20set%20of%20cells%20(C4%3AC22)%20instead%20of%20only%20one%20cell%20C4.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20use%20of%20min%20function%20is%20to%20serve%20other%20purpose.%20The%20overall%20picture%20is%20that%2C%20I%20have%20a%20set%20of%20numbers%20which%20represent%20a%20test%20scores%20between%200%20and%2050.%20What%20I%20want%20to%20find%20is%20the%20highest%20score%20below%20the%20succeed%20point%20which%20is%2025.%20For%20example%2C%20If%20I%20have%20the%20scores%3A%2010%2C20%2C23%2C30%2C40%2C49%3B%20the%20value%20I%20need%20to%20find%20should%20be%2023%20in%20this%20example.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%2C%20C40%3DConstant%3D25.%20Thanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487491%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487491%22%20slang%3D%22en-US%22%3EYour%20new%20logic%20is%20now%20entirely%20different%20from%20the%20old%20one.%20Can%20please%20provide%20a%20clear%20example%20of%20your%20data%20and%20desired%20result%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487532%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487532%22%20slang%3D%22en-US%22%3E%3CP%3EOk%2C%3C%2FP%3E%3CP%3ELets%20us%20assume%20we%20have%20the%20following%20students%20scores%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStudent%20No.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Score%3C%2FP%3E%3CP%3E-------------%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20----------%3C%2FP%3E%3CP%3E%26nbsp%3B1%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2041%3C%2FP%3E%3CP%3E2%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2020%3C%2FP%3E%3CP%3E3%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2023%3C%2FP%3E%3CP%3E4%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2027%3C%2FP%3E%3CP%3E5%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2030%3C%2FP%3E%3CP%3E6%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2010%3C%2FP%3E%3CP%3E7%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2049%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20test%20maximum%20possible%20score%20is%2050.%20That%20is%20the%20minimum%20success%20degree%20that%20student%20need%20to%20take%20for%20passing%20the%20exam%20is%2025.%3C%2FP%3E%3CP%3EI%20need%20to%20find%20thclosest%20student%20score%20to%2025%20for%20failed%20students%20(below%2025).%3C%2FP%3E%3CP%3EIn%20this%20above%20example%2C%20the%20required%20score%20will%20be%2023%20for%20student%20no.%203...%3C%2FP%3E%3CP%3EWhat%20I%20am%20doing%20is%20that%20storing%20the%2023%20in%20a%20specific%20cell%2C%20say%20cell%20C40.%20and%20the%20students%20score%20are%20along%20column%20C.%3C%2FP%3E%3CP%3Ethen%2C%20follow%20anyone%20of%20the%20following%20algorithms%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20find%20the%20minimum%20difference%20between%20C40-value%20and%20the%20student%20score%20which%20is%20bellow%20C40%3C%2FP%3E%3CP%3E2)%20find%20the%20maximum%20value%20of%20student%20score%20which%20is%20still%20bellow%20C40.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487589%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487589%22%20slang%3D%22en-US%22%3EPlease%20further%20expand%20your%20assumptions%20by%20filling%20in%20the%20results%20you%20want%20in%20Column%20D.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487599%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487599%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Twifoo%3A%20There%20is%20no%20column%20D%2C%20the%20following%20is%20what%20exactly%20happen%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStudent%20No.%20Score%3C%2FP%3E%3CP%3E-------------%20----------%3C%2FP%3E%3CP%3EC1%20%3D%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2041%3C%2FP%3E%3CP%3EC2%20%3D%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2020%3C%2FP%3E%3CP%3EC3%20%3D%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2023%3C%2FP%3E%3CP%3EC4%20%3D%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2027%3C%2FP%3E%3CP%3EC5%26nbsp%3B%20%3D%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2030%3C%2FP%3E%3CP%3EC6%20%3D%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2010%3C%2FP%3E%3CP%3EC7%20%3D%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2049%3C%2FP%3E%3CP%3E.%3C%2FP%3E%3CP%3E.%3C%2FP%3E%3CP%3E.%3C%2FP%3E%3CP%3EC40%20%3D%2025%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EC41%20%3D%20closest%20score%20to%20C40%20value%20and%20lower%20than%20C40%20value.%3C%2FP%3E%3CP%3E%26nbsp%3BThis%20means%2C%20C41%20cell%20have%20the%20formula%20I%20want%20to%20use%20to%20fulfill%20the%20required%20task.%3C%2FP%3E%3CP%3EIn%20summary%2C%20there%20is%20a%20set%20of%20scores%20and%20I%20want%20to%20find%20the%20closest%20score%20to%20C40%20value%20and%20lower%20than%20C40%20value%20using%20one%20formula%20and%20store%20this%20value%20is%20cell%20C41.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487620%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328396%22%20target%3D%22_blank%22%3E%40m81hassan_51%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20the%20desired%20result%20is%20a%20secret.%3C%2FP%3E%3CP%3EOne%20last%20guess%3A%3C%2FP%3E%3CPRE%3E%3DMAXIFS(C4%3AC33%2CC4%3AC33%2C%22%26lt%3B%22%26amp%3BC40)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487636%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487636%22%20slang%3D%22en-US%22%3EI%20am%20getting%20the%20following%20error%20with%20this%20gess%3A%20%23NAME%3F%20..%3CBR%20%2F%3EThis%20means%20that%20there%20is%20no%20such%20command%20in%20Excel.%3CBR%20%2F%3EMay%20you%20please%20double%20check%20the%20tool%20name%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487640%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487640%22%20slang%3D%22en-US%22%3ESimilar%20to%20Detlef%E2%80%99s%20formula%2C%20but%20backward-compatible%3A%3CBR%20%2F%3E%3DMAX(INDEX((C4%3AC33%3CC40%3E%3C%2FC40%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487664%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487664%22%20slang%3D%22en-US%22%3EThis%20one%20is%20working%2C%20but%20it%20gives%20a%20wrong%20answer.%20Let%20me%20experiment%20with%20this%20formula%2C%20I%20think%20I%20will%20get%20something%20useful%20from%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much%20Twifoo%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-487687%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20IF%20statement%20in%20Excel%20with%20set%20of%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-487687%22%20slang%3D%22en-US%22%3EC4%3A33%20refers%20to%20the%20range%20of%20scores.%20You%20may%20have%20to%20modify%20that%20range%20if%20it%20is%20different%20from%20the%20actual%20range%20where%20the%20scores%20are%20stored.%20Good%20luck!%3C%2FLINGO-BODY%3E
m81hassan_51
Occasional Contributor

Hi,

I need to use the if statement with set of cells, say C4:C33, and the return for TRUE condition is the same as cell value, while for false condition the return value is zero. The following is an explanation:

 

=MIN(IF(C4:C33<C40,C4:C33,0))

 

However, this form is giving invalid value because I am using the same set as a True result.

Anyone can give an idea how to do this please?

 

Thanks

14 Replies

@m81hassan_51 

Maybe this?

=MINIFS(C4:C33,C4:C33,"<"&C40)

 

Perhaps, this is what you need:
=(MIN(C4:C$33)<C$40)*C4

Thanks for your reply, What I exactly want, is that, if the value of specifc cell in column C is lower than the fixed value (placed in cell C40) the the output from If-statement is the same value of specific C-cell, else, the output value is zero. C40 is only used for condition/comparesion and not used as an output neither in True nor in false conditions.

 

Thanks

 

You need no MIN. Thus, what you need is:
=(C4<C$40)*C4
The above formula returns either C4 or 0.
What I need is to use a set of cells (C4:C22) instead of only one cell C4.

The use of min function is to serve other purpose. The overall picture is that, I have a set of numbers which represent a test scores between 0 and 50. What I want to find is the highest score below the succeed point which is 25. For example, If I have the scores: 10,20,23,30,40,49; the value I need to find should be 23 in this example.

Here, C40=Constant=25. Thanks
Your new logic is now entirely different from the old one. Can please provide a clear example of your data and desired result?

Ok,

Lets us assume we have the following students scores:

 

Student No.       Score

-------------       ----------

 1                           41

2                            20

3                            23

4                            27

5                            30

6                            10

7                            49

 

the test maximum possible score is 50. That is the minimum success degree that student need to take for passing the exam is 25.

I need to find thclosest student score to 25 for failed students (below 25).

In this above example, the required score will be 23 for student no. 3...

What I am doing is that storing the 23 in a specific cell, say cell C40. and the students score are along column C.

then, follow anyone of the following algorithms:

 

1) find the minimum difference between C40-value and the student score which is bellow C40

2) find the maximum value of student score which is still bellow C40.

 

Thanks a lot for your help

 

 

Please further expand your assumptions by filling in the results you want in Column D.

Hi Twifoo: There is no column D, the following is what exactly happen:

 

Student No. Score

------------- ----------

C1 =                  41

C2 =                 20

C3 =                  23

C4 =                  27

C5  =                30

C6 =                 10

C7 =                 49

.

.

.

C40 = 25

 

C41 = closest score to C40 value and lower than C40 value.

 This means, C41 cell have the formula I want to use to fulfill the required task.

In summary, there is a set of scores and I want to find the closest score to C40 value and lower than C40 value using one formula and store this value is cell C41.

 

Thanks

 

@m81hassan_51 

It seems the desired result is a secret.

One last guess:

=MAXIFS(C4:C33,C4:C33,"<"&C40)

 

I am getting the following error with this gess: #NAME? ..
This means that there is no such command in Excel.
May you please double check the tool name?
Similar to Detlef’s formula, but backward-compatible:
=MAX(INDEX((C4:C33<C40)*C4:C33,0))
This one is working, but it gives a wrong answer. Let me experiment with this formula, I think I will get something useful from it.

Thank you very much Twifoo
C4:33 refers to the range of scores. You may have to modify that range if it is different from the actual range where the scores are stored. Good luck!
Related Conversations