Need value as per given criteria

Solved/Closed
Sanam Khan - Jun 20, 2015 at 06:56 PM
Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 - Jun 23, 2015 at 09:56 PM
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

Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Jun 21, 2015 at 12:25 PM
Sanam Khan, good morning.

Try to use it, like this one:

Is this what you want.
I hope it helps.
0
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
0
Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Jun 21, 2015 at 01:38 PM
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
0
Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Jun 22, 2015 at 01:31 PM
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.
0
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
0
Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Jun 23, 2015 at 09:56 PM
Sanam Khan, Good evening.

I fell glad for your feed back.

Have a nice day.
0