Determine final result from various category

problem 1.PNG

Please see the attachment. I'm sorry that i can't upload excel file.

UPDATE:
Here is the attachment of how i do it. Is there any more sophisticated single formula how to do it?

Next time, do try to upload a file.

In the meantime, here is a possible solution. I reorganised your layout into something a little more logical.

Instructions on how to attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

Try using Match() and Index() formulas.

See attached for one way to do this.

Cheers

What would be the expected result if Profit was 51 bil or unit was 151 or if all three conditions were met in Example 1?

Amit, that doesn't work. You have reversed the order of the rankings. A rank of 1 = BIG not SMALL.

You are right Glenn. The way to fix that is to reverse the array in row 4:

Rank | 999 or an arbitrarily large number | 250 | 50

And change the +1 to -1 in last Match() in the formula.

Cheers

Have you looked at the solution in post 2?

I did... and it works. :-) I saw it after I posted mine.

Aactually, I was writing to the OP...

Using the cstom foormatting for billions, try this (very minor rearrangement).