Excel VBA Course
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.)

View Course

Wanted to know how to use formula for this

0

Hello Guys, I'm very new here.

I'm learning basic excel as of now...

Just wanted to know what formula to be used here(attanched is the file) to get the data in next colum.

Could I pelase get a help in the formula for the attahced sheet?

Complete details updated in excel sheet.

Thanks in advance.

Answer
Discuss

Answers

0

You have picked quite a difficult task for yourself to start out with. Here is the formula that will do what you want.

=INDEX({"A","B","C","D","E"},MATCH($B16,{1000,0.95,0.9,0.85,0.8},-1))

To understand this formula you (after you look up the INDEX and MATCH functions) you need to know that data between curly braces are ranges. You could write the letters from A to E in A1:A5 or A1:E1 and then replace {"A","B","C","D","E"} with $A$1:$A$5 or $A$1:$E$1. In the same way you could replace {1000,0.95,0.9,0.85,0.8} with a range of worksheet cells containing these same values. To do so would be better for a number of reasons the most important of which is that formulas should manipulate data, not contain them. However, for explaining what is happening the above view is much preferrable.

BTW your formula seems to contain a logical error which Excel's MATCH function has difficulty duplicating. You want > 0.95 = "A" and <0.95 to be "B". My above function complies with your first condition but grades =0.95 as "B". If you want >=0.95 to be grade "A" you must reverse the MATCH part of the function and build the INDEX array accordingly.

=INDEX({"E","D","C","B","A"},MATCH($B16,{0,0.8,0.85,0.9,0.95},1))

Observe the final "1" or "-1" in the MATCH functions.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login