using negatives in a formula

[ReyH]

Hello, ive looked everywhere for help and its been a few days. i've tried messing around with some formulas but im too new and couldnt find what i was looking for. Basically, i want to use negative numbers in my formula and have no idea how. this is what i came up with: =IF(H10>-800, "F", IF(H10>-750, "D-", IF(H10>-700, "D", IF(H10>-650, "D+", IF(H10>-600, "C-, IF(H10>-550, "C", IF(H10>-500, "C+", IF(H10>-400, "B-", IF(H10>-300, "B", IF(H10>-100, "B+", IF(H10<100, "A-", IF(H10<250, "A", IF(H10<400, "A+"))))))))))))

right now, its not correct. theres obviously (to some) an error in this. ive used it before and it kinda worked but im not sure how to do this. so i want to GRADE the value of each numbers


<400=A+
<250=A
<100=A-
(-100)-99=B+
(-300)-(-101)=B
(-400)-(-301)=B-
(-500)-(-401)=C+
(-550)-(501)=C
(-600)-(-551)=C-
(-650)-(-601)=D+
(-700)-(-651)=D
(-750)-(701)=D-
>=(-751)=F


im not good at math, im not good at excel.. the grades are for differences in the sum of two numbers.

 

[ravi2628]

hi

have you tried if statement with and function,

just a sample of 3 to 4 logic taken for your reference

Book1
	A	B
1	-101	B
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(A1>=400,"A+",IF(A1>=250,"A",IF(A1>=100,"A-",IF(AND(A1<=99,A1>=-100),"B+",IF(AND(A1<=-101,A1>=-300),"B")))))

 

[Rick Rothstein]

Consider a number that is larger than any of your values, say -100, well that number is also greater than -800 so your first test traps it immediately and you never get to the range you actually want to trap it in. The problem is you are testing in the wrong order... rewrite your formula with the test reversed from what you now have (the test for greater than -800 should be the last test you do, not the first.

 

[ReyH]

Consider a number that is larger than any of your values, say -100, well that number is also greater than -800 so your first test traps it immediately and you never get to the range you actually want to trap it in. The problem is you are testing in the wrong order... rewrite your formula with the test reversed from what you now have (the test for greater than -800 should be the last test you do, not the first.

Ah, ok i understand! thank you. i'll try it out right now

 

[ReyH]

hi

have you tried if statement with and function,

just a sample of 3 to 4 logic taken for your reference

Book1
	A	B
1	-101	B
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(A1>=400,"A+",IF(A1>=250,"A",IF(A1>=100,"A-",IF(AND(A1<=99,A1>=-100),"B+",IF(AND(A1<=-101,A1>=-300),"B")))))

ok let me give it a try really quickly. thank you for the fast reply btw!

 

[ReyH]

=IF(A1>=400,"A+",IF(A1>=250,"A",IF(A1>=100,"A-",IF(AND(A1<=99,A1>=-100),"B+",IF(AND(A1<=-101,A1>=-300),"B")))))

IT WORKED. Thank you.. i swear i tried all day, i was wondering how to use multiple functions in the same cell. but now i learned a few things just now. thank you a lot.

 

[ravi2628]

IT WORKED. Thank you.. i swear i tried all day, i was wondering how to use multiple functions in the same cell. but now i learned a few things just now. thank you a lot.

You are welcome and thanks for the feedback