Applying multiple conditions to formula

Question

I have a situation where I need to apply several "value boundaries" to several different "starting positions" and then subsequently output the "ongoing position".

The "value boundaries" are: <500, >=500<750, >750<=850 and >850

The "starting positions" are "Basic", "Standard", "Standard+", and "Platinum",

The value boundaries are then applied and the "ongoing position" outputted,

e.g. a person starting at "Basic" achieves 600 and then moves to "standard", or someone at "Platinum" gets 700 so reverts to "Standard+" etc etc, I have tried this with a nested IF to no avail .

Edit: it seems the solution is beyond a formula do I need VBA to solve this one?

Answer 1

If I understand you correctly, you will input two numbers and, based on where these numbers fall with regard to your boundary conditions, the ongoing position will be determined. Furthermore, the ongoing position cannot be more than "one different" from the starting position.

That being the case, and with the First Score in D1, and the Second Score in E1, the following formula will, I think, output the ongoing position:

=CHOOSE(MATCH(D1,{0;500;750;850})+SIGN( MATCH(E1,{0;500;750;850})-MATCH( D1,{0;500;750;850})),"Basic","Standard","Standard+","Platinum")

Answer 2

O.K....Put the sequence of values in column A. In B1 enter:

=IF(A1<500,0,IF(AND(A1>=500,A1<750),1,IF(AND(A1>=750,A1<800),2,3))) and copy down

this will generate a code 0 thru 3 to represent the four levels

In C1 enter:

=B1

In C2 enter:

=IF(ABS(B1-B2)<2,B2,IF(B2>B1,B1+1,B1-1)) and copy down

column C implements the one-step limit. Finally in D1 enter:

=CHOOSE(C1+1,"Basic","Standard","Standard+","Platinum") and copy down

This converts the codes to word-levels

Answer 3

If it is ok for you to have a table with the ranges somewhere, this will work for you.

Cell F1 contains the formula (as text) that is used in E1. Then you can copy-paste downwards.

Answer 4

I struggled to get an answer to this but eventually with the help of the suggestions I was able to work it out, here is the finished formula:

=IF(AND(H4="Basic",G17<450),"Basic",IF(AND(H4="Basic",G17>=450),"Standard",IF(AND(H4="Standard",G17<450),"Basic",IF(AND(H4="Standard",G17>449,G17<700),"Standard",IF(AND(H4="Standard",G17>=700),"Standard+",IF(AND(H4="Standard+",G17>699,G17<800),"Standard+",IF(AND(H4="Standard+",G17<700),"Standard",IF(AND(H4="Standard+",G17>=800),"Platinum",IF(AND(H4="Platinum",G17<800),"Standard+","Platinum")))))))))

Answer 5

Without a VLOOKUP() table:

=IF(A1<500,"Basic",IF(AND(A1>=500,A1<750),"Standard",IF(AND(A1>=750,A1<800),"Standard+","Platinum")))

NOTE:

In VBA, the And syntax is slightly different.