Need value as per given criteria [Solved/Closed]

Report
-
Posts
1927
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
June 17, 2021
-
Hello,
There is a range given on one column and on the other column there are points. Ex:
200 - 180. 10 points
179 - 160. 9.5 points
159 - 130. 8 points

How I put a formula in excel when I write 168 it should reflect 9.5 points in front of that. And if I change the value to 141 it should reflect 8 points. I have a long range starting from 0.
Could you please assist me?


4 replies

Posts
1927
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
June 17, 2021
143
Sanam Khan, good morning.

Try to use it, like this one:

Is this what you want.
I hope it helps.
Hello Mazzaropi, Thanks for your help but its not working.

Please see/download the file from the link below. I need to take out points in yellow highlighted field.

https://drive.google.com/file/d/0BxfIO6gperXld1Izd0NBMzMxX1U/view
Posts
1927
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
June 17, 2021
143
Your file need a password to access ???

Save your file at a free site like www.speedyshare.com and put the link to download here
Posts
1927
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
June 17, 2021
143
Sanam Khan, Good afternoon.

Your table of values has a very unusual way to write.

Try to use:
M4 --> =SUMPRODUCT(($E$5:$E$20>=L4)*($F$5:$F$20<=L4)*($G$5:$G$20))
Copy it down.

Is this what you want?
I hope it helps.
Hello Mazzaropi,

Thanks a lot for your help and support. You are Genius. You are Excel Master. Thank you so much.. You made my life. The formula is working perfectly.

Please keep in touch with me at facebook.com/https://www.facebook.com/sanam.ahmed.khan
Posts
1927
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
June 17, 2021
143
Sanam Khan, Good evening.

I fell glad for your feed back.

Have a nice day.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!