CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Lizi1990
Lizi1990

Reputation: 103

How to create an Array Formula with AND

I am facing here an issue that in my workbook. I am using IF(AND()) in a formula to find the range between two values. As my sheet is large, I have to drag the equation from top to bottom, and there is a chance someone may accidentally erase the formula.

Can you help me to make an array formula so that I can lock that top cell? I hope this will make my sheet little faster. I have a sample sheet here in which I can drag the "If & And" formula up to B8.

https://docs.google.com/spreadsheets/d/1Wy7ilXvSv7eY27kZpuiIax6XRKbZKs1bUS5pzgtOOkM/edit#gid=0

Thank you

Upvotes: 0

Views: 78

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34420

I would tend to use Vlookup for these where the lookup range is either on another sheet:

=ArrayFormula(if(A3:A="","",vlookup(A3:A,Lookup!A1:B10,2)))

or in the formula itself:

=ArrayFormula(if(A3:A="","",vlookup(A3:A,{0,5;11,4;20,3;40,2;60,1;80,0;100,"***"},2)))

enter image description here

This is the Lookup sheet:

enter image description here

Upvotes: 0

General Grievance
General Grievance

Reputation: 5033

The issue is that AND doesn't like to play nice with ranges. Luckily, you can use arithmetic operators to work around this:

=ArrayFormula(IF(A3:A="","",
  IF((A3:A>=0)*(A3:A<=10),5,
  IF((A3:A>=11)*(A3:A<=19),4,
  IF((A3:A>=20)*(A3:A<=39),3,
  IF((A3:A>=40)*(A3:A<=59),2,
  IF((A3:A>=60)*(A3:A<=79),1,
  IF((A3:A>=80)*(A3:A<=99),0,
  ""))))))
))

Simple rules:

  • AND -> Multiplication
  • OR -> Addition

Just make sure you wrap comparisons in parens, or the Order of Operations will cause it to fail.

Of course, you can always use some kind of step function formula to get what you want if there's a nice pattern to the intervals, like here.

Upvotes: 2

Related Questions