IF function

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080
Hi

Im looking for help using the IF function

I want A1 display the value from various cells depending on the value of B1.

For example, if B1 is 2.37 then I want to display the value of E1.

Now I know the formula for this is:
Code:
=IF(B1=2.37,E1,0)

and if B1 is greater than 2.37 then display E2 is:

Code:
=IF(B1>2.37,E2,0)

I also know to use the < sign within the formula.

What I want to know is how do I display a value of a cell if the cell in question needs to be between two different values. eg.

I want A1 to display the value of E3 if it is between 2.38 - 3. 56.

Thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
The simple answer is to use AND like

=IF(AND(B1>2.37,B1<=3.57),E3)

but there may be a simpler way if you have a whole series of ranges, e.g.

=LOOKUP(B1,{0,2,4,6},E1:E4)

this will give you E1 if B1 is >=0 and < 2, E2 if B1 >=2 and < 4 etc.
 

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080
Im unsure of what to use

this is my data

what i want in A1 is

if A2 is <1.10 then display D1
if A2 is between 1.11 & 1.25 display D2
if A2 is between 1.26 & 1.38 display D3

it goes on a bit but that is what Im trying to achieve

thanks
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi glerwell:

In your situation, I would use a Lookup Table ... as illustrated in the following ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJ
1ASSUMPTIONLookup Table
21.05D1if A2 is9.90E-307D1
3if A2 is between 1.11 & 1.25 display D21.11D2
4if A2 is between 1.26 & 1.38 display D31.26D3
5it goes on a bit but that is what Im trying to achieve1.39tbd
6
Sheet16


</body></html>
 

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080

ADVERTISEMENT

I can't get any to work, I keep getting circular references.

never mind I'll just enter id manually its no big deal.

thanks anyway
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
What formula did you try and where are you putting it?

I'm assuming that A2 will always be >=0 and that when you say, D1, D2 and D3 you mean the contents of cells D1:D3.

Try

=LOOKUP(A2,{0,1.1,1.26},D1:D3)

or, if you actually want to display "D1" etc. in the result cell

=LOOKUP(A2,{0,1.1,1.26},{"D1","D2","D3"})
 

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,080
the values D1,D2,D3 etc are dependent on a sum of another cell eg. F5, F5 is a sum of other cells. So that's why I'm having problems, I don't think its anything to do with the formulas
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,086
Messages
5,857,280
Members
431,868
Latest member
servox

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
Top