Nested IF Formula returning wrong result?

[HCANNI819]

Afternoon All, I hope you can help.

I'm trying to automate the posting of data which requires different information in one field (in this case the Tender), based upon what range of numbers the Job No falls within.

As you can see from the below, I'm trying to get D4 to provide the result of PRE08.
I have tried using a Nested IF formula, but despite the Job No being <205000, it is returning the X for no match instead of the PRE08 as expected. I've had a play with cell formatting on the 204007 cell but it still isn't making any difference.

Any advice would be greatly appreciated as it's frustrating me!

Thanks

 

[dreid1011]

Are your Job No's numbers stored as text? That may be the issue.

 

[Snakehips]

Agree, job number looks like text.
If that is how your data needs to be then maybe try

=IF(N(C4)<200000,"PRE09",IF(N(C4)<205000,"PRE08",IF(N(C4)<206000,"LAB02","X")))

 

[HCANNI819]

Thanks both - I had changed it to Number on this tab, but it was coming through from another tab formatted as Text.

@Snakehips - out of interest what does the (N do on that formula?

 

[Snakehips]

It coverts the text number to a number as for as the formula is concerned.
Similarly you could use

=IF(--C4<200000,"PRE09",IF(--C4<205000,"PRE08",IF(--C4<206000,"LAB02","X")))

or

=IF(1*C4<200000,"PRE09",IF(1*C4<205000,"PRE08",IF(1*C4<206000,"LAB02","X")))

 

[dreid1011]

 

[HCANNI819]

Perfect thank you - thats probably easiest way for me to resolve these!

Cheers, have a good evening.