Hi Zubin

Try this.

Sunny

Beautiful solution as always Sunny! And exactly what Zubin wanted.

However I would prefer working with variables for the grade/percentage and date. Is much 'stronger' for working in the future if there are changes coming in percentages or dates and such.

But that's more a thing of 'how to work with solid sheets' and for now the question is solved I think, but we'll hear from Zubin.

Thanks Frans for your comments .

Whether to work with variables (named ranges) or otherwise will depend on the project. For bigger projects I would normally have a Setup sheet with such variables/constants defined where any changes will affect other sheets. Users will not be allowed to edit the formulas in the workbook.

For others, I would use formulas that can easily be understood and maintained by the users themselves. No array formulas, mega formulas etc unless I have no other choices.

When answering posts I have no idea what is the Excel knowledge level of the OP. I can only make a guess based on the questions asked. In such a situation, I would go for the easiest formula. It may be longer but at least the OP can edit the formulas themselves without much problem. This is just my opinion.

Wishing you and your family a Merry Christmas and a Happy 2017 New Year!!!!.

Sunny

Best wishes to you and the people who are near to you as well! And I understand your motivation and agree with it. On the other hand I doubt it if not so experienced Excel users understand this IF and OR nested formulas. We'll see!

I hope you receive some reaction from Zubin on your work. Sometimes........

Hi Frans

You can see my 2nd formula. It should be easier to change even though it looks more complex than my 1st.

INDEX & MATCH! I'll bet Zubin loves this one 🙂 🙂

Clever though!

Off-Topic:

Hi Frank,

I do understand your enthusiasm, but if we want to have a useful forum for our members and visitors, we have to post only on-topic messages, otherwise we will become a chat room 🙂 , and from my experience, when someone is desperately searching for a solution, it will be discouraged to continue reading our forum, he feels like we wasted his time.

I'm sure you will understand, let's do our best to create a useful and technical forum!

Happy Holidays

Catalin

Hi Friends,

Thank you all for the comments and suggestions. I tried out Sunny's formula and it is working perfectly. Thank you Sunny. 

Happy New Year

Zubin

Catalin Bombea said
Off-Topic:

Hi Frank,

".........................I do understand your enthusiasm, but if we want to have a useful forum for our members and visitors, we have to post only on-topic messages, otherwise we will become a chat room 🙂 ................" and: "............. I'm sure you will understand, let's do our best to create a useful and technical forum!............"   

Hi Catalin, you're right! I was enthousiast and think this was something adding value to the topic. But I'll look more careful in the future, cause I understand your point very well. Sunny gave two rather different approaches to the same question, both maybe not so easy for the average user, but both beautiful solutions. I bet he wouldn't have given the second one if I didn't tease him on this 🙂

But we'll try to stay on topic next times (this one being the last not on topic....).

A good 2017 also to you and your people!

Frans