I have a list of shops with annual sales. These shops have each been assigned a discount code.
The discount code is based on sales but is fixed within a range. For example, all shops that sell up to 999 could have a discount code of either 1,2,or 3. shops that sell between 1,000 - 2,000, they could have a code of 4, 5 or 6. and so on.
I am trying to get a formula that would check the sales in one cell, check the discount in another cell, and then return say 'correct Band' if the discount code is correct for the amount of annual sales, and if not, it would return 'incorrect band'.
So for example, using the figures above Shop 1: sales 1,500, assigned discount code = '5', formula should return 'Correct Band' Shop 2: sales 600, assigned discount code = '4', formula should return 'Incorrect band' Shop3: sales 750, assigned discount code = '3', formula should return 'Correct Band'
I have tried so many different things, but none work. I have tried IF, AND.......IF,OR......tried nested ISNUMBER(SEARCH......... Some seem to work, but generally my problem is that I cannot find a way to specify that Band 2 should be greater than or equal to 1000.........so when I put in <1000 for band 1 and <2000 for band 2 and I get a shop which has sold 1400 but has been assigned a discount code of 3, I am unable to pick it up in my formula.
Hope the above makes sense and sorry it is so wordy.
Thanks
If I have correctly understood your problem, I'd say sometihng like this, using nested if
if(and(sales<1000;discount code<=3);"correct band";if(and(sales>=1000;sales<2000;discount code>3);"correct band";"wrong")
this hould work if the discount code is in a numeric format, otherwise either you turn it into a number or you edit the above code in
Clearly when I mean "sales" and "discount_code" i mean the cells in the corresponding columns
if(and(sales<1000;or(discount_code="1";discount_code="2";discount_code="3"));"correct band";if(and(sales>=1000;sales<2000;or(discount_code="4";discount_code="5";discount_code="6"));"correct band";"wrong")
try and let me know