I Got two formulas to get my final score which works perfectly fine and get me the result that im expecting, However i just wanted to check is there further easy formula to do this, I'm looking for a formula approach only since i can make the user defined function myself.
What basically im trying to do is rate the score that i got from a score of 1 to 5 on a incremental basis and incremental starts from first limit and ends at the second last limit.
Enclosed is the sample file for your reference.
Saurabh...
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Patrick,
You dont have to copy it and sort in the ascending order because if you see Column-i and k i have sorted them in ascending order. Thanks for posting but instead of lookup in this you have used a vlookup and then a range formila to do the same thing, sorry for not being clear at the first time by Simplifying i mean shorting the formula further then exisiting to what i have or using some other logic which is different then what im doing.
Saurabh
.......although I note that should you change the percentages so that they are less evenly distributed, e.g. instead of 59%, 62%, 65% and 68% you use 57%, 63%, 65% and 76% then I think my suggestion works but Saqib's doesn't.......
(given a "straight line" interpolation between each two percentages)
barry
Saqib,
From Work, i work for a US based MNC so primilarly my work timings are in evening hours my time which is Morning time US hours. Also i tried the formula and it worked till the time barry said the % are evenly distrubuted when i change them it goes crazy, However this still gives me an idea to use array which i will do possible once im back to office by refining the formula that you gave to get the figures out but appreciate your effort over this.
Barry, Thanks for testing this out and helping me over this.
Saurabh...
Business Accounts
Answer for Membership
by: matthewspatrickPosted on 2009-09-08 at 09:34:53ID: 25283589
Hello Saurabh,
I copied your rating grid to F11:K17, and sorted it on the From column ascending. I then used this formula
to replicate your result:
=IF(OR(MATCH(C4,$F$13:$F$1
I like that, because if you ever end up with more rows in the grid, you would just need to update the range
references, but the logic stays the same.
BTW, if you want to allow prorating to the scores with a rating of 1...
=IF(C4>=MAX($F$13:$F$17),M
Regards,
Patrick