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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

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