[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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?
0
guidx
Asked:
guidx
  • 2
3 Solutions
 
zorvek (Kevin Jones)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
 
ssisworoCommented:
with formula :

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


and here is screen shot :
 screen
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, SyedEngineerCommented:
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now