Between function

GRCY1988

New Member
Joined
Jun 18, 2015
Messages
7
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top