If and between-values in Numbers for iWork or in Excel.

Hi I have a question about how to do something in Numbers in iWork and or in Excel. I want to make a cell do this:

If x - Y = between 0-4 then return 100. If x - y = between 5-9 then return 50. If not any of those return 0.

Is it possible to so something like that in numbers or in Excel?
LVL 1
guidxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ssisworoConnect With a Mentor Commented:
with formula :

 "IF(A12-B12 <= 4, 100, IF( A12-B12 <= 9, 50, 0))"


and here is screen shot :
 screen
0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
=IF(AND(A1-B1>=0,A1-B1<=4),100,IF(AND(A1-B1>=5,A1-B1<=9),50,0))

Where A1 is x and B1 is y.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
A simpler formula:

=CHOOSE(MIN(A1-B1,3),100,50,0)

Where A1 is x and B1 is y.

Kevin
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Kevin, I do not see how your simpler formula will give the desired result.

guidx, Here is my formula

=VLOOKUP(A1-B1,{0,100;5,50;10,0},2)

Saqib
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.