Inserting an IF or OR Logic in current formula

[searchingforhelp]

Hi,

Wondering if I can receive help regarding the formula below.

My goal is to expand this formula: =IF(B3>=0,"Within Policy",IF(B3<0,"Out of Policy")) to recognize “not applicable” in the results field.

For example:

With or out of Policy.xlsx
	A	B
1	Within Policy/ Out of Policy	Results
2	Within Policy	3
3	Out of Policy	-3
4	not applicable	not applicable
5	Within Policy	2
Test environment

Cell Formulas
Range	Formula
A2:A3,A5	A2	=IF(B2>=0,"Within Policy",IF(B2<0,"Out of Policy"))

 

[Fluff]

Maybe

=IF(ISTEXT(B2),B2,IF(B2>=0,"Within Policy",IF(B2<0,"Out of Policy")))

 

[jtakw]

Hi,

You can do it this way:

Book3.xlsx
	A	B
1	Within Policy/ Out of Policy	Results
2	Within Policy	3
3	Out of Policy	-3
4	not applicable	not applicable
5	Within Policy	2
Sheet850

Cell Formulas
Range	Formula
A2:A5	A2	=IF(ISNUMBER(B2),IF(B2>=0,"Within Policy","Out of Policy"),B2)

 

[searchingforhelp]

Excellent, it works! Thanks!

I should of added this to the original post. This is a separate formula from the one given. Non- related. Can you also suggest how I can add not applicable to the below =SUM formula?

Thanks!

With or out of Policy.xlsx
	A	B
1	Numbers	Results
2	5	-1
3	3	1
4	not appliable	#VALUE!
5	1	3
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=SUM(A2-4)*-1

 

[Fluff]

What value do you want?

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B
1	Numbers	Results
2	5	-1
3	3	1
4	not appliable	not appliable
5	1	3
Sheet851

Cell Formulas
Range	Formula
B2:B5	B2	=IF(ISNUMBER(A2),-(A2-4),A2)

 

[searchingforhelp]

Thank you! Should I check both responses as they are solutions?

 

[jtakw]

I think you can only check 1...

 

[Fluff]

Should I check both responses as they are solutions?

As jtakw has said you can only check one, you need to check either post#2 or post#3 as they answered your original question.