• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF Statements with Multiple 'Greater Than Equal to' Formulas

B

Brandon Carraway

New Member
Hello,

I am trying to do something that is *moderately* complicated.
I am trying to produce an output based on a cell's data.

EX)
I have 2 columns, "Store" and "Market".
In the 'Store' column, I have a range of data (1-12, 261-277, 111-118), and 'Market' will have an output based on 'Store'.

If 'Store' is on or between '1-12-, then 'Market' needs an output of "1 HCFP".
If 'Store' is on or between '261-277', then 'Market' needs an output of "3 HCFP".
If 'Store' is on or between '111-118', the 'Market' needs an output of "2 SFP".

-This might not actually be complicated, but I just cannot seem to figure it out (some time on Google / just playing around with "IF" and "AND" statements).

Hopefully this is clear enough, and if there is already a thread with something similar to this that I did not see, then please let me know.

Thank you in advance,
Brandon Carraway
 
You've written them logically and you look close enough to core idea as to how to go about it. Without seeing your data this would be outside guess of what you need ;)
=IF(AND(A2>=1,A2<=12),"1 HCFP",IF(AND(A2>=111,A2<=118),"2 SFP",IF(AND(A2>=261,A2<=277),"3 HCFP","Other Case!")))

If it doesn't meet your requirement then try posting some sample data.
 
Hi,

I don't think 'And' operator required in the if statements except the first one

=IF(AND(A2>=1,A2<=12),"1 HCFP",IF(A2<=118,"2 SFP",IF(A2<=277,"3 HCFP","Other Case!")))
 
shrivallabha said:
You've written them logically and you look close enough to core idea as to how to go about it. Without seeing your data this would be outside guess of what you need ;)
=IF(AND(A2>=1,A2<=12),"1 HCFP",IF(AND(A2>=111,A2<=118),"2 SFP",IF(AND(A2>=261,A2<=277),"3 HCFP","Other Case!")))

If it doesn't meet your requirement then try posting some sample data.
Click to expand...

Thank you very much for your reply - This looks almost like the formula I used
(=IF(AND(D679>=0,D679<=12),"1 HCFP",IF(AND(D679>=261,D679<=277),"3 HFP",IF(AND(D679>=111,D679<=118),"2 SFP"))))

The crazy part is - The formula only works for only one cell, no others.

In my example, I am using columns 'A' through 'E', but in my actual work but, the data falls into 'D' through 'W' (Some hidden unimportant columns).

I have attached an example data with both my formula, and yours. :)

Thank you again!
 

Attachments

Sathish KV said:
Hi,

I don't think 'And' operator required in the if statements except the first one

=IF(AND(A2>=1,A2<=12),"1 HCFP",IF(A2<=118,"2 SFP",IF(A2<=277,"3 HCFP","Other Case!")))
Click to expand...

As the OP explicitly stated that values needed to be between boundaries which are not consecutive the use of AND for the second and 3rd condition is mandatory
 
Sathish KV said:
Hi,

I don't think 'And' operator required in the if statements except the first one

=IF(AND(A2>=1,A2<=12),"1 HCFP",IF(A2<=118,"2 SFP",IF(A2<=277,"3 HCFP","Other Case!")))
Click to expand...
OP has clearly stated the cases.
"2 SFP" is to be shown if the value is between 111 and 118 inclusive.
So if you enter 110 then it should show Other case! at least in my opinion.
 
Hi,

May be late entry for myself, you data in range A2:A4 are number entered as text, that is the reason you are not getting proper result, use --A2 in your formula to get correct result.

Regards,
 
Brandon Carraway said:
EDIT:
The removal of the leading '0's as well as changing the data range to 'Numbers' fixed my errors.

Thank you all sooo much for the rapid responses and genius suggestions!


OK - UPDATE:
The removal of the leading '0's fixed the formula for the number range of '1-12'.
'111-118' and '261-277', however, are not working.
Click to expand...
 
I suspect text formatting. You do not need to treat for OCD. If you see tiny green triangle in the top left corner where the formula errors you have numbers stored as text.

Just format the column using
Format | Custom
and type 000
You should get what you want the underlying data will be numerical.
 
You must log in or register to reply here.
Back
Top