How do I set up a greater than but less than formula in excel?

M

Millmut

I am tring to sort data to look at one of three tables. Once it finds the
right table, then it produces a value based on the table. I can get the ones
to work for <50000 and >100000, but I am having trouble establishing the
criteria for the >499999 but >100000.

Here are my tables:
0-$49999 $50,000-$99,999 $100,000 and
over

0.000% 9.999% 0.05 0.000% 9.999% 0.05 0.000% 9.999% 0.05
10.000% 19.990% 0.04 10.000% 19.990% 0.04 10.000% 19.990% 0.04
20.000% 29.990% 0.03 20.000% 29.990% 0.03 20.000% 29.990% 0.03
30.000% 39.990% 0.02 30.000% 39.990% 0.02 30.000% 39.990% 0.02
40.000% 49.990% 0.01 40.000% 49.990% 0.01 40.000% 49.990% 0.01
50.000% 59.990% 0 50.000% 59.990% 0 50.000% 59.990% 0
60.000% 69.990% -0.02 60.000% 69.990%-0.02 60.000% 69.990% -0.02
70.000% 79.990% -0.03 70.000% 79.990%-0.035 70.000% 79.990% -0.04
80.000% 89.990% -0.04 80.000% 89.990% -0.05 80.000% 89.990% -0.06
90.000% 99.990% -0.05 90.000% 99.990%-0.065 90.000% 99.990% -0.08
100.000% -0.06 100.000% -0.08 100.000% -0.1

Therefore, excel needs to look for a value to find the correct table, and
then needs to look at another number to determine the correct result. I am
not sure how to write If (cell ) is >49999<100000. This obviously did not
work. Do I need to break it out into 2 sentences? Any ideas?
 
I

Ikaabod

To code:

If ActiveCell > 49999 And ActiveCell < 100000 Then
MsgBox "Yes"
Else
MsgBox "No"
End If

For a formula:
=IF(AND(A1>49999,A1<100000),"Yes","No")
 
D

Duncan

=SUM(IF(B2>=49999,IF(B2<=100000,0),0))


assuming B2 is the cell, when you put this into whatever cell you want
the answer in, click on the text and do control+shift+enter and this
will put curly brackets around the sum to make it work. you can add
more 'IF's in to sum based on more criteria (like your tables)
following the same pattern as above.

HTH

Duncan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top