What is the order of operations for nested IF & AND statements over a range of cells

[anl299]

Asking my question will make more sense if I just show you what I'm trying to do. In the picture below you can see the different categories for classes. What I want is just a box (offscreen) that lists every category that has no grades BUT is still being used to calculate the final grade. For example, column K, the one labeled Final should be listed because row 4 has 0 in it but row 6 has a non zero value. Columns #06-#09 have nothing in row 4, BUT since they all have zero in row 6 they should be ignored.

If I use nested IF and AND functions, the logic test looks like this:

IF(AND(B4:K4=0, B6:K6<>0), true, false)

The problem is, it doesn't work. I think it reads the whole range of B through K (row 4) checks if they're all equal to zero, and then it reads the entire range of B through K (row 6), K6 returns false and it's done. The outer IF statement only runs a single time, checking every box in both rows simultaneously.

Is there a way for me to get the if/and statement to check B4 and B6 together, then C4 and C6 together, then D4 and D6...

Also, I do not want to individually select every cell, I would much rather use ranges.

Thanks, if you're not sure you understand what I'm saying let me know and I will try to explain differently or show more of my excel logic.

[#2 · Accepted solution]

@anl299 

=IFERROR(INDEX($B$2:$K$2,SMALL(IF(($B$4:$K$4=0)*($B$6:$K$6<>0),COLUMN($B:$K)-1),ROW($A1))),"")

This formula could be an alternative and it returns the categories in cell E9 and below in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

[#3]

This is almost exactly what I need, the only issue is that for me it only returns the first value that meets the criteria. In my file, if I had only columns #07 and Final with values of 0 but with a weight, it would only return "#07". Same thing if I made the columns #07, #08, #09 and Final have a weight but no scores, it would only return "#07". I expect (without testing) that if my file had your data, it would only return "header4" while ignoring 8, 9 and 10.
I don't know if there is some overflowing block happening that preventing the rest of the name from showing or what.

Also, I haven't had the time to really look into your code. I'm half decent at coding, but extremely new to excel so I've never used any of the formulas or functions you used. I might be able to figure it out given enough time, but if you had any ideas right off the bat, I'm all ears

Thanks.

[#4]

@anl299 

In my first reply i unfortunately forgot to mention that the formula must be dragged down from cell E9. In my sample file i entered the formula in cell E9 and confirmed it with ctrl+shift+enter. Then i dragged the formula down across range E9:E18. Since there are 4 categories with a weight and no score (in the sample file) these are now returned in range E9:E12. Cells E13:E18 are empty because the formula uses the IFERROR function to return an empty cell instead on an error message. If the entries for the weight and score are changed for a category the results update dynamically in range E9:E18. If all 10 categories have a weight and no class then all are listed in range E9:E18.

Do you work with a recent version of Excel such as Excel 2021, Office 365 or Excel for the web? If so then there are other possible solutions for this task with formulas such as TEXTJOIN and FILTER.

[#5]

@anl299 

As variant for the legacy Excel in F9

=IFERROR(
    INDEX($2:$2,
        AGGREGATE(15,6,
            COLUMN($B$2:$K$2)/($B$4:$K$4=0)/($B$6:$K$6<>0),
            ROW()-ROW($F$8))
    ),
"" )

and drag it down. No Ctrl+Shift+Enter is required.

On Excel 365 or 2021

=TOCOL( FILTER(category, (score=0)*(weight<>0) ) )

[#6]

I got it to work.
Thanks!