Forum Discussion
Formula Help - IF/OR/AND
Hello - I am trying to work through a formula for a much larger dataset and I've created an example which I will attach to this discussion post. Basically, I want to create a formula where if the "Gr...
Formula in cell N4 and filled down:
=INDEX($D$10:$D$15,MATCH(1,(J4>$B$10:$B$15)*(J4<=$C$10:$C$15),0))
Formula in cell O4 and filled down:
=INDEX($D$10:$D$15,MATCH(1,(K4>$E$10:$E$15)*(K4<=$F$10:$F$15),0))
You can try these formulas. Enter the formulas with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
These calculations are somewhat mysterious!
I called the initial value ranges 'A' and 'B' respectively and looked up 'east' and 'west' from graded data. I defined 'score' to be the descending values 12 to 2. As far as I could make out the return values might be
= XLOOKUP(east, PERCENTILE.INC( A, quintile), score, , 1)
= XLOOKUP(west, PERCENTILE.INC( B, quintile), score, , 1)
where I have calculated the quintiles in the formula rather than looking them up.
- Thanks Peter!
