CODEWITHSUNDEEP
excel-formula
Ray
Ray

Reputation: 86

How to use an IF function in Excel formula with conditions consisting of both negative and positive numbers

I have an excel sheet with values in both negative and positive sign. Based on the values, I want them to assign a category. The excel sheet looks like this

I have to apply the formula on the SPI values.

enter image description here

I am using an IF statement.

The formula I am using is:

=IF(C3>=2,"EW",IF(1.5<=C3<=1.99,"VW",IF(1<=C3<=1.49,"MW",IF(-0.99<=C3<=0.99,"NN",IF(-1.49<=C3<=-1,"MD",IF(-1.99<=C3<=-1.5,"SD","ED"))))))

The problem is that every time it shows only "ED". It is as if it is skipping all the conditions and running only the last case. How can I fix this?

Upvotes: 0

Views: 2905

Answers (3)

Solar Mike
Solar Mike

Reputation: 8375

Try this, but check the values:

IF(C3>=2,"EW",IF(C3>=1.5,"VW",IF(C3>=1,"MW",IF(C3>=-0.99,"NN",IF(C3>=-1.49,"MD",IF(C3>=-1.99,"SD","ED"))))))

enter image description here

You could also consider vlookup() like this:

enter image description here

Which will be easier to maintain...

Upvotes: 1

Pomul
Pomul

Reputation: 390

Unlike many programming languages, Excel can't handle defining two delimiter at once for a variable. This won't work:

=IF(C3>=2,"EW",IF(1.5<=C3<=1.99,"VW", ...)

Since excel does not recognize 1.5<=C3<=1.99, it considers the statement as FALSE and checks what to do in that case, which at the end of your IF statement is "ED".

In your case, you have to use AND():

=IF(C3>=2,"EW",IF(AND(C3>=1.5,C3<=1.99),"VW", ...)

I haven't tested Solar Mike's solution, but his answers are generally pretty good. (Though the part with IF(C3>=--1.49,... probably needs to be rewritten to IF(C3>=-1.49,...) :)

Upvotes: 0

Vokail
Vokail

Reputation: 691

You can understand better what is going on using a beautifier (for example this) so your formula:

=IF(
    C3 >= 2,
    "EW",
    IF(
        1.5 <= C3 <= 1.99,
        "VW",
        IF(
            1 <= C3 <= 1.49,
            "MW",
            IF(
- 0.99 <= C3 <= 0.99,
                "NN",
                IF(
- 1.49 <= C3 <=- 1,
                    "MD",
                    IF(
- 1.99 <= C3 <=- 1.5,
                        "SD",
                        "ED"
                    )
                )
            )
        )
    )
)

From your example is not clear which columns and row are, could you provide also excel file ?

Upvotes: 0

Related Questions