IF function

G

glerwell

Well-known Member
Joined
Jun 25, 2006
Messages
1,082
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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"})
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Tell me the status based on a percentage
Replies
3
Views
62
etaf
etaf
M
  • Question
Countif function criteria
Replies
5
Views
146
mrsushi
M
S
  • Question
Multiple Conditions - Greater than less than
Replies
6
Views
316
Peter_SSs
Peter_SSs
S
  • Question
Function to repeatedly replace string one-at-a-time via a lookup table
Replies
4
Views
116
smallxyz
S
T
  • Solved
Find column of value changed
Replies
4
Views
185
Fluff
Fluff

Forum statistics

Threads
1,214,641
Messages
6,120,685
Members
448,977
Latest member
dbonilla0331

Share this page

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