Excel Formula Question (help needed)

Question

Hello

I love this help section and hoping someone can guide me on the correct formula to use.

I want to:

* return a value of "In Range" if A2 is within range of A3 (min) and A4 (max)

* return a value of "Low" if cell A2 is less than A3 (min)

* return a value of "Excess" if A2 is greater than A4 (max)

i.e.

A3 is 5 (min) & A4 is 15 (max)

Value in A2 = 10, return value should be "In Range"

Value in A2 = 4, return value is "Low"

Value in A2 is 20, return value is "Excess"

Thanks in advance!

Answer 1

I can't duplicate the issue you are describing. Whether A3 and A4 are 0's or blank, I still get the result "Excess"

I even tried entering the A2 data as text and entering text into A3 and A4. It did disrupt the formula results but did not result in an #N/A.

First thing would be to check the formula carefully to be sure it is still referring to the correct cells and have the correct compare symbols. I am cautious to offer changes to the formula without understanding why it is getting the error. The only thing I could do is to add an IFERROR component to the formula, but without knowing the cause, I don't know what other effects that might have. You can try this and see if it works better without any other ill effects.

=IFERROR(IFS(AND(A2>=A3,A2<=A4),"In Range",A2<A3,"Low",A2>A4,"Excess"),"Excess")

The last "Excess" could be changed to anything inside the quotes if that ends up being a better option.

EDIT:

I did get the #N/A if the compare symbol in the last condition is wrong, either < or =. That results in the formula not finding any TRUE condition which is when the #N/A occurs so check those symbols very carefully.

Answer 2

Hello Rich~M

I found my error. Totally my fault. Had an extra character in my formula. Once I corrected it, it works perfectly.

THANK YOU!!!!

I will mark this as resolved.

Regards,

Rod

Answer 3

Hello Rich~M

This is great and worked well. Thank you very much! I am using this to determine if our inventory level is within the min/max range, is low or has excess.

One issue I found: when A3 and A4 have a value of 0 (meaning the inventory is no longer needed), the formula returned a value of "#N/A" and not "Excess"

i.e.

A3 is 0 (min) & A4 is 0 (max)

Value in A2 is 20, return value should be "Excess"

Is there another statement to include in the formula or minor change to avoid this error and have it return "Excess"?

Thank you again!

Answer 4

Here is the formula to identify the ranges as you have them listed. It is entered in B2 below.

=IFS(AND(A2>=A3,A2<=A4),"In Range",A2<A3,"Low",A2>A4,"Excess")