A1=Bottom/Middle/Top

B1=Bottom/Middle/Top

C1=(Yes or No)

D1=(5 or -5)A1 is the current performance group. B1 will be the new performance group. I want a formula that says if A1 is "bottom" and becomes "middle" in B1, C1 will say Yes or No then D1 will say 5 if C1 is Yes. Same goes if A1 is "middle" and becomes "top" in B2.

On the other hand, if A1 is "top" or "middle" and B1 becomes "middle" or "bottom", C1 says Yes or No, then D1 will say -5

The logic is if a bottom performer moves up to middle or middle performer becomes top performer, he'll get 5 points. But if top performer becomes middle or middle becomes bottom, he loses 5 points.

I'm sorry I don't know how to simplify this but I'm hoping there's an excel formula that can make this easier for me.

✔ Best Answer

OK, I think this will work for you. First allow me to explain something, some of which you may know, but since I don't know what you know, I'll start at a pretty basic level. In the computer environment all letters, numbers, symbols, etc. have an ASCII code associated with them. (I'll let you look that up if you want more info.) For example, if you enter an Q in A1 and then use =CODE(A1) in another cell, the result will be 81 because that is the ASCII code for Q. Now, if you enter Quebec in A1 and then use =CODE(A1) in another cell, the result will still be 81 because the CODE function only checks the first character of a string or number.

With that as the background, let's look at your situation:

Bottom ---> ASCII code for "B" is 66

Middle ---> ASCII code for "M" is 77

Top ---> ASCII code for "T" is 84Luckily for you, the first letters of your three levels just happen to have ASCII codes that are in the same ascending order as your words. Therefore, we can use Greater Than or Less Than to compare the "words". We don't even have to check the ASCII code ourselves, Excel will do that for us.

To see that for yourself, enter this in a cell:

="Bottom"<"Middle"

Internally, Excel will (sort of) take these steps:

1 - ="Bottom"<"Middle"

2 - ="B"<"M"

3 - =CODE("B")<CODE("M")

4 - =66<77 which will return TRUE.Therefore, according to Excel, Bottom is "Less Than" Middle and Middle is "Less Than" Top. Perfect!

What this all means is that we can use your exact words and let Excel tell us the performance comparison.

Here is what I did:

I created a Data Validation Drop Down list in A1 and B1. The three choices are Bottom, Middle, Top.

In C1 I used this formula: =IF(A1>B1,"No","Yes")

In D1 I used this formula =IF(C1="No",-5,5)

Excel will compare A1 with B1 and return the values you asked for.

Note 1: From an Excel perspective, you don't really need the Yes-No cell. You could return the -5 and 5 by comparing A1 with B1 directly:

=IF(A1>B1,-5,5)

However, if you want the Yes-No as a "visual" that's fine.

Note 2: While you are "updating" A1 and B1, you may not get the correct answer. For example, if both A1 and B1 show Middle at the same time, C1 will show Yes, because A1 is not Greater Than B1 at that point in time. If you want to deal with that more elegantly, you could use something like this:

=IF(A1=B1,"No Change",IF(A1>B1,"No","Yes"))

How you deal with D1 in that situation is up to you.

Let me know if you have any other questions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

re: "A1 is "bottom" and becomes "middle" in B1"Could you explain what you mean by "becomes"?

A1 can not "become" something in B1.

Please explain your process as to how these cells "become" whatever it is that they "become".

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Oh I realized I made I mistake. What I meant by "becomes" is when we enter a data, so kindly disregard. The logic is still the same if say b1 is middle and a1 is bottom, it means the individual moved up or if b1 us middle and a1 is top, it means the individual's performance goes down. I hope I am making myself clear.

OK, now that you've explained that part, I still don't understand what the Yes/No in C1 has to do with this.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

I want C1 to say Yes if B1 say the individual has moved up from Bottom to Middle or Middle to Top referencing it from A1(say wk 1 performance) and B1 (wk 2 performance) and No if individual has moved down from Top to Middle or Middle to Bottom.

OK, I think this will work for you. First allow me to explain something, some of which you may know, but since I don't know what you know, I'll start at a pretty basic level. In the computer environment all letters, numbers, symbols, etc. have an ASCII code associated with them. (I'll let you look that up if you want more info.) For example, if you enter an Q in A1 and then use =CODE(A1) in another cell, the result will be 81 because that is the ASCII code for Q. Now, if you enter Quebec in A1 and then use =CODE(A1) in another cell, the result will still be 81 because the CODE function only checks the first character of a string or number.

With that as the background, let's look at your situation:

Bottom ---> ASCII code for "B" is 66

Middle ---> ASCII code for "M" is 77

Top ---> ASCII code for "T" is 84Luckily for you, the first letters of your three levels just happen to have ASCII codes that are in the same ascending order as your words. Therefore, we can use Greater Than or Less Than to compare the "words". We don't even have to check the ASCII code ourselves, Excel will do that for us.

To see that for yourself, enter this in a cell:

="Bottom"<"Middle"

Internally, Excel will (sort of) take these steps:

1 - ="Bottom"<"Middle"

2 - ="B"<"M"

3 - =CODE("B")<CODE("M")

4 - =66<77 which will return TRUE.Therefore, according to Excel, Bottom is "Less Than" Middle and Middle is "Less Than" Top. Perfect!

What this all means is that we can use your exact words and let Excel tell us the performance comparison.

Here is what I did:

I created a Data Validation Drop Down list in A1 and B1. The three choices are Bottom, Middle, Top.

In C1 I used this formula: =IF(A1>B1,"No","Yes")

In D1 I used this formula =IF(C1="No",-5,5)

Excel will compare A1 with B1 and return the values you asked for.

Note 1: From an Excel perspective, you don't really need the Yes-No cell. You could return the -5 and 5 by comparing A1 with B1 directly:

=IF(A1>B1,-5,5)

However, if you want the Yes-No as a "visual" that's fine.

Note 2: While you are "updating" A1 and B1, you may not get the correct answer. For example, if both A1 and B1 show Middle at the same time, C1 will show Yes, because A1 is not Greater Than B1 at that point in time. If you want to deal with that more elegantly, you could use something like this:

=IF(A1=B1,"No Change",IF(A1>B1,"No","Yes"))

How you deal with D1 in that situation is up to you.

Let me know if you have any other questions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

DerbyDad03 Thank you so much. I actually didn't know about the codes. You explained it to me clearly. I now have a working file. You are an Excel Guru!

I'm glad I could help. I would like to offer another option, actually more of a concept/technique that you may be able to use in the future. As I noted in my previous post, you were "lucky" that the words you were using just happened to start with letters that had ASCII codes that were in the same ascending order as Bottom, Middle, Top. What if you weren't so lucky and were using different words that had to be ranked but where the ASCII codes didn't work out so nicely?

For example:

A 1 Red ---> ASCII code for "R" is 82 2 Yellow ---> ASCII code for "Y" is 89 3 Green ---> ASCII code for "G" is 71In that case, you couldn't use the CODE function to rank those words in a Green, Yellow, Red order because the ASCII codes are not in the correct order.

What I would do is create a "Helper Table" using these formula:

E F 1 =IF(A1="Green",3,IF(A1="Yellow",2,1)) =IF(B1="Green",3,IF(B1="Yellow",2,1))Now you have created a table that has numbers that are in the same ascending order as your words. You can then refer to the cells in your Helper Table that contain the numbers instead of the cells that contain the words.

C 1 =IF(E1>F1,"No","Yes")The main point here is that if you have data for which you just can't find a function that fits or just can't get to the final result in one step, consider using "Helpers" as an intermediate step.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Follow up question:

=IF(A1=B1,"No Change",IF(A1>B1,"No","Yes"))

Is there a way that C1 will show blank if B1 doesn't have a data yet?Same as this formula: =IF(A1>B1,-5,5)

Is there a way that it would show blank if mo data has been entered?

Just add another IF function to check for B1="". Double quotes with nothing between them indicates that either the cell is empty or that it contains a "null string". Try this formula:

=IF(B1="","",IF(A1=B1,"No Change",IF(A1>B1,"No","Yes")))

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History