If number is between formula?

themightyreds

Board Regular
In column A I have weights ranging from 1 - 1500.

In Column B I what to return the whether the weight is -100, +100, +300, +500 or +1000.

is it possible to write an If formula using between?

All help appreciated!!

Matt

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

schielrn

Well-known Member
Yes, you would so something like:

=if(and(x > y, x < z),"x is between y and z","x is not between y and z")

Hope that helps.

themightyreds

Board Regular
erm he says scratching his head? no I'm still struggling.

=if(and(x > y, x < z),"x is between y and z","x is not between y and z")

I can't seem to make that fit?

schielrn

Well-known Member
I'm not really sure what you are asking in your original question or what makes the results return? Can you provide any sample data and expected results?

lenze

Legend
Try this
Code:
``=IF(A1<100,"Less than 100",IF(A1<300,"Between 100 & 300",IF(A1<500,"Between 300 & 500",IF(A1<1000,"Between 500 & 1000","Greater than 1000"))))``

lenze

themightyreds

Board Regular
Thanks Lenze works exactly as I need it to. I was thinking that I needed to find a formula which uses between you have shown me that by not meeting the conditions of the first IF it automatically creates the between situation for me! Genius!

lenze

Legend
I'm glad you caught on. I've found that one of the hardest concepts of IF to get across. I have, in fact, run into college profs teaching Excel who do not grasp it!!
lenze

Replies
2
Views
68
Replies
3
Views
236
Replies
5
Views
156
Replies
0
Views
384
Replies
2
Views
124

1,190,700
Messages
5,982,362
Members
439,776
Latest member
mathewduffy

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?

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

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