Hello everyone
I have a question.
I can't wrap my head around how I should make a formula for the following thing I need.
So basically I need to check if a value in a cell is in any of these ranges, I just don't know how to put so many ranges in one formula.
if it's between the two numbers it needs to give the value next to it, if it's over 305 it needs to say "NOT OKAY"
the ranges & values:
My first try was this(M7 being the value needed to be checked and the cells being the values):
=IF(M7=AND((M7>=0),(M7<=20)),J5,IF(M7=AND((M7>=21),(M7<=40)),J6,IF(M7=AND((M7>=41),(M7<=70)),J7,IF(M7=AND((M7>=71),(M7<=100)),J8,IF(M7=AND((M7>=101),(M7<=140)),J9,IF(M7=AND((M7>=141),(M7<=180)),J10,IF(M7=AND((M7>=181),(M7<=230)),J11,IF(M7=AND((M7>231),(M7<=270)),J12,IF(M7=AND((M7>=271),(M7<=305)),J13,IF(M7>305,M7*J14))))))))))
but this one and other adjusted ones didn't work out for me.
If anyone has a simpler working method.
Please let me know!
Thanks in advance.
Alex
I have a question.
I can't wrap my head around how I should make a formula for the following thing I need.
So basically I need to check if a value in a cell is in any of these ranges, I just don't know how to put so many ranges in one formula.
if it's between the two numbers it needs to give the value next to it, if it's over 305 it needs to say "NOT OKAY"
the ranges & values:
0 - 20 / 170 |
21 - 40 / 196 |
41 - 70 / 212 |
71 - 100 / 227 |
101 - 140 / 253 |
141 - 180 / 263 |
181 - 230 / 279 |
231 - 270 / 304 |
271 - 305 / 320 |
305 - ...... / NOT OKAY |
My first try was this(M7 being the value needed to be checked and the cells being the values):
=IF(M7=AND((M7>=0),(M7<=20)),J5,IF(M7=AND((M7>=21),(M7<=40)),J6,IF(M7=AND((M7>=41),(M7<=70)),J7,IF(M7=AND((M7>=71),(M7<=100)),J8,IF(M7=AND((M7>=101),(M7<=140)),J9,IF(M7=AND((M7>=141),(M7<=180)),J10,IF(M7=AND((M7>=181),(M7<=230)),J11,IF(M7=AND((M7>231),(M7<=270)),J12,IF(M7=AND((M7>=271),(M7<=305)),J13,IF(M7>305,M7*J14))))))))))
but this one and other adjusted ones didn't work out for me.
If anyone has a simpler working method.
Please let me know!
Thanks in advance.
Alex