SOLVED

# Formula Help - IF/OR/AND

Copper Contributor

# 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 "Graded Data" for the respective A or B dataset falls in > or <= to the Percentiles then it will receive the respective score. I've tried numerous If(And(Or)) statements to no avail. I'm trying to create a seamless formula where I can drag it down to score each one based upon where they fall within the percentiles. Any help would be appreciated,

9 Replies

# Re: Formula Help - IF/OR/AND

Depends on which version of Excel you are. Legacy formula could be

best response confirmed by John23785 (Copper Contributor)
Solution

# Re: Formula Help - IF/OR/AND

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.

# Re: Formula Help - IF/OR/AND

This worked very well! I have office 365 but this solution was quick and easy, thank you @SergeiBaklan . Question, If I were to have 4 arrays A-D, with different respective percentiles, is there a way to merge them? If A=A then score or if not then go to B-D?

# Re: Formula Help - IF/OR/AND

Thank you @Oliver this worked equally as good as the above. Do you have any advice to my question above as well? Thanks in advance,

# Re: Formula Help - IF/OR/AND

@John23785 , you are welcome.

Afraid I didn't catch what A=A means, perhaps you may clarify on the sample.

# Re: Formula Help - IF/OR/AND

@SergeiBaklan I believe I figured it out with a series of nested If statements, appreciate your help

# Re: Formula Help - IF/OR/AND

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.

# Re: Formula Help - IF/OR/AND

Thanks Peter!
1 best response

Accepted Solutions
best response confirmed by John23785 (Copper Contributor)
Solution

# Re: Formula Help - IF/OR/AND

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.