200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hello everyone, I'm facing a weird problem I've never faced before. I'm trying to use a simple, nested IF-function for ONE cell, but it applies to multiple cells which can't be removed. It works like that: (WENN = IF in German)
=WENN(B26:B30="gering";"gering";
WENN(B26:B30="mittel";"mittel";
WENN(B26:B30="hoch";"hoch")))
It will apply to as many cells as I'm checking (B26:B30 = 5)
I've attached the file. Formula is written in B32 and I want it to apply only in B32.
Thanks in advance!
Hi
Your equation is an array formula, acting on a 5 cell array and producing a five cell array output (as you have found).
I suggest you wrap the OR function around each test (which produces a TRUE or FALSE result, like a normal IF, not an array) and then the result appears in just one cell.
In the attached file, I've done that by putting this in cell B32 of sheet Prozessbewertung:
{=IF(OR(B26:B30="gering"),"gering",IF(OR(B26:B30="mittel"),"mittel",IF(OR(B26:B30="hoch"),"hoch")))}Hope this is what you want.
Revision 1: Following the discussion point made by @Variatus, you may need to change the OR for AND (if that's the result you need)
Revision 2: I now realise that "hoch" is "high" (and "gering" is "low") so you probably want "hoch" as the result if any cell in B26:B30 rates "hoch" for "Komplexität" (complexity). Therefore I've put this alternative array equation in cell C32 of the revised file attached to swap the nested IF tests to suit:
{= IF(OR(B26:B30="hoch"),"hoch",IF(OR(B26:B30="mittel"),"mittel",IF(OR(B26:B30="gering"),"gering")))}