# Between function

#### GRCY1988

##### New Member
I am trying to write a formula to return different numbers based on where another number falls. For example:
cell a10 = 4.75%
cell a11 = 5.07%

How do I write a nested function if a number falls between values and I want to add a value to that number in another cell?

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Assuming the value you are testing is in cell B10, this formula should work for you...

=IF(AND(B10>=A10,B10<=A11),B10+5%,B10)

Note: As written, the values in A10 and A11 are included in the range being tested... remove the equal sign from either, or both, if they are not to be included in the "between" range.

Your query is a bit vague, but here is an example formula that says IF the value in A10 is greater than or equal to 4% but less than 5% (i.e. between 4 and 5), then add 2% to it, otherwise, show a blank entry.

=IF(AND(A10>=4%,A10<5%),A10+2%,"")

If you meant something else, then you'll have to be much more specific

Welcome to the board!

You could do it one of several ways, the first 1 that comes to mind is

=IF(AND(B1<=A11,B1>=A10),B1+C1,C1)

Where B1 holds the value to compare, and C1 holds the value to add it to.

Assuming the value you are testing is in cell B10, this formula should work for you...

=IF(AND(B10>=A10,B10<=A11),B10+5%,B10)

Note: As written, the values in A10 and A11 are included in the range being tested... remove the equal sign from either, or both, if they are not to be included in the "between" range.
I should have mentioned, although I think it might have been obvious, that I assumed the amount to be added to the value being tested was 5% if the value was actually between the test values in cells A10 and A11.

Replies
5
Views
348
Replies
50
Views
4K
Replies
3
Views
418
Replies
1
Views
494
Replies
7
Views
576

1,196,042
Messages
6,013,045
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back