Excel IF function help

Question

I have been trying to come up with the correct IF function for this but cannot get it right I keep getting #NAME errors.

We will call the corresponding cell F3 and it represents order total and I'm trying to find a function that represents the following:

If order total is less than or equal to 100, 0 points

If order total is greater than 100 but less than or equal to 200, 10 points

If order total is greater than 200 but less than or equal to 300, 20 points

If order total is greater than 300 but less than or equal to 400, 30 points

If order total is greater than 300 but less than or equal to 500, 40 points

I tried this and it didn't work

=if(F3<=100,”0”,if(F3<=200,”10”,if(F3<=300,”20”,if(F3<=400,”30”,”40”))))

and 

=if(0<F2<=100,”0”,if(100<F2<=200,”10”,if(200<F2<=300,”20”,if(300<F2<=400,”30”,if(400<F2<=500,”40”)))))

Answer 1

PS.... Alexander wrote:

=if(F3<=100,”0”,if(F3<=200,”10”,if(F3<=300,”20”,if(F3<=400,”30”,”40”))))

and 

=if(0<F2<=100,”0”,if(100<F2<=200,”10”,if(200<F2<=300,”20”,if(300<F2<=400,”30”,if(400<F2<=500,”40”)))))

The first formula is the best way to implement the intended logic, except for trying to quote the results, as I noted previously.

The second formula is incorrect syntax insofar as Excel will not interpret as you intended.  For example 0<F2<=100 is interpreted as (0<F2)<=0, which is FALSE<=0 or TRUE<=0, depending on the value in F2.  Either way, the result is FALSE because Excel considers all logic values (TRUE or FALSE) to be greater than any number.

The way to implement that logic in the second formula is syntax of the form AND(0<F2,F2<=100).

But that is overkill in this case because Excel evaluates the first formula left-to-right, stopping with the first true condition.

Answer 2

That really helped, thank you much!

Answer 3

You get the #NAME error because the character you are using to quote the results is ASCII 148 instead of ASCII 34 (double-quote), which is ".

But why do you quote the results of the IF expression, in the first place?  For example, why return the string "0" instead of the number 0 (zero)?  Are you aware of the difference?  Are you consciously choosing the difference; that is, with purpose?

Normally, I would expect:

=if(F3<=100,0,if(F3<=200,10,if(F3<=300,20,if(F3<=400,30,40))))

FYI, that could be written more "economically".  Are you interested?