Scale

[deflord]

hello! greetings from Portugal. I need to create a formula that according to the value in a cell will turn the values in a scale.

this will be the scale

if the number in cell b15 is between 1 and 3 the output value is 1, if the number in cell b15 is between 3 and 4,5 the output value is 2, if the number in cell b15 is between 4.5 and 5 the output value is 3, if the number in cell b15 is between 5 and 5,5 the output value is 4, an finally if the number in cell b15 is bigger than 5.5 the output value will be 5.

i did this formula

=IF(OR(B15=0);"";IF(OR(B15<=3);1;(IF(OR(3<B15;B15<=4,5);2;IF(OR(4,5<B15;B15<=5);3;IF(OR(5<B15;B15<=5,5);4;IF(OR(B15>5,5);5)))))))

the problem is if the value more than 3 i allway get the return value of 2
(

[sweep]

Hi,

Try this

=INDEX({1;2;3;4;5};MATCH(B15;{1;3;4,5;5;5,5;9.99E+307};1))

you can alter the array in the match function to move the cut off points as required.

[deflord]

sorry didn't help. can you see if there's any mistake in the formula i posted. it seens ok.

[sweep]

You have OR statements where they're not needed, which won't help, and some OR statements where there should be AND statements.

[deflord]

could you try to correct the formula please?