A consultant asks us to consider Star Order Proposal B which classifies orders according to "5 Star" (more than $1,000), "4 Star" (more than $500, up to $1000) and simply blank ($500 or below).
Which formula i can use for this statement?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
IF
-1
Answers
0
I would use an INDEX/MATCH function like the one given below.
=INDEX({3,4,5},MATCH(A2,{0,500,1000},1))&" Star"The array for the index, Array(3, 4, 5), matches
the array for the sales, Array(0, 500, 1000) - meaning "more" than 0, 500 or 1000.
Therefore it's easy to set up, easy to read, easy to understand and easy to maintain. For most cases those are all the requirements one can put to an Excel formula.
You can use =MATCH(A2,{0,500,1000},1) as a separate function. Try it out. It will return 1, 2 or 3, depending upon the number in A2. The above formula uses the result of the MATCH to return the 1st, 2nd or 3rd itm from the INDEX array.