Between Symbol in Excel – does this option exist in Excel to use in your formulas?
This week’s hint and tip is about how you can ‘create’ a between symbol in Excel. There are a number of symbols in Excel and when comparing cells the greater than and less than ones are used a lot. We often get asked if there is a between symbol similar to these two and unfortunately no, but you are able to ‘create’ one using a formula. This formula is covered on our Advanced Excel training course but we decided to do a hint and tip on it with its use in this example too. We are going to go through it now below.
The AND function
The AND function in Excel is a logical function that returns an outcome of TRUE if all the arguments are true and FALSE if at least one argument is false. We can use this AND function to test if something is greater than or equal to and less than or equal to something. This essentially is a way of testing for ‘between’ in Excel.
Using a ‘between’ symbol in an IF function
A reminder of the layout of the IF function can be seen below with explanation of its parts:
=IF(logical test,Value_if_true,Value_if_false)
The logical test is what you are looking for or your ‘question’. Then the ‘Value_if_true’ part is the outcome of the test is true. Finally the ‘Value_if_false’ part is if the outcome of the test is false.
Often the logical test is looking to test if something is greater than or less than something, but you can also test for if something is between something. There isn’t a symbol for this, you have to use the AND function and nest it within the IF function. We’re going to show this below.
Worked example
We are now going to create a formula that allows us to test for ‘between’. As there is no actual symbol on your keyboard for this, we are using the AND function to ‘create’ this symbol. This is then being nested into the logical part of the IF function.
The formula we are going to use is shown below:
=IF(AND(B1>=E1,B1<=E2),B1*E3,E4)
The parts of the formula are explained below:
- Logical test: AND(B1>=E1,B1<=E2). This function is testing if B1 is greater than or equal to E1 and also if B1 is less than or equal to E2
- Value if true: B1*E3. The outcome if it is true, a percentage calculation to work out the discount
- value if false: E4. The outcome if it is false, nothing/zero
As the AND function will only return a TRUE if all the arguments in it are true, this formula will only return TRUE if the cell B1 is ‘between’ the two cells E1 and E2.
The video below explains how you can create this ‘between’ symbol and then use it within your formulas in Excel.
Watch the video below to find out more and then try it out on your computer!
We hope you have enjoyed this hint and tip. Why not take a look at our previous one on using the Evaluate Formula option in Excel?