Converting if/and statement into an array formula

Question

I'd like to convert the following formula:

=IF(A2:A=1,"Under an hour",IF(AND(A2:A<48,A2:A>23),"1 Day",IF(A2:A>=24,int(A2:A/24)&" Days",ROUNDUP(A2:A) &" Hours")))

into an Array formula. Currently it doesn't work because AND statements don't work in Array Formulas but I'm not really sure how to get it working without the and (and without embedding 24 different IF statements)

I basically just want to return user-readable text in column B relating to how many hours are shown in A:

0   0 Hours
1   Under an hour
2   2 Hours
23  23 Hours
24  1 Day
25  1 Day
47  1 Day
48  2 Days
49  2 Days
100 4 Days

Presumably this is not Excel since your range syntax would only work in Sheets? — Rory
– Rory, Commented May 24, 2021 at 11:15
Yeah, sorry, it's sheets, I didn't realise the range syntax was different, Should I untag Excel or does the if statement work the same in both? — JoeW
– JoeW, Commented May 24, 2021 at 11:22

basic · Accepted Answer · 2021-05-24 12:17:16Z

2

Change AND(A2:A<48,A2:A>23) to (A2:A<48)*(A2:A>23):

answered May 24, 2021 at 12:17

basic

12k2 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JoeW Over a year ago

Amazing! Do you understand why this works? What would you need to do to an OR statement?

basic Over a year ago

@JoeW AND is an array function - it receives an array of parameter values and returns a single value, so it will not work with ARRAYFORMULA as you intended. In turn, multiplication works with each element of the array separately and in this case also returns the array. And IF function converts zero values to FALSE and nonzero to TRUE.

basic Over a year ago

@JoeW For OR change mutiplication to addition, i.e. * to +.

Collectives™ on Stack Overflow

Converting if/and statement into an array formula

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related