Single-Cell Array Formulas

• Building up to more complex things
• You can use array computations to reduce "extra" rows.
• Go to the data sheet
• Assume I want to count the number of letters in column a
• I could
  • Insert a column after a
  • enter the =len(a2) function b2
  • Copy this down
  • Then add this column
• Or I could
  • Enter =sum(len(name)) as an array formula.
• small and large functions.
  • These functions select the k^th smallest or largest values in a range of numbers.
  • so =large(win, 1) will return the top winner.
  • But what if I want to select the top four
    • Select s2:s5
    • =large(win,{1,2,3,4}) as an array function
    • But can we do this with a loop from the last section?
    • =large(win,row(indirect("1:4")))
    • How about, can you generate positions n through n+3
    • Put 1 in a cell r2
    • =large(win, row(indirect(r2 & ":" & r2+3)))
    • Check to see if changing r2 works.
  • 
  • 
• I would like to build a query to find how many candies match a given attribute count.
• Let's start by building a countif
  • In cell R8 put a 1
  • in cell s8 put =countif(a_count, r8)
  • in cell t8 put =sum(if(a_count=r$8,1,0)) as an array formula
  • What happens if you don't put this in as an array formula?
  • 
  • In cell u8 put =sum(if(a_count=r$8,1))
• I would like to build a lookup area
  • I want to put the rank in cell t12
  • I want the name of the corresponding value in cell t13
  • In S12 put "Rank"
  • In S13 put "Row"
  • In S14 put "Name"
  • In T12 put 1
  • In T13 put =max(if(a_rank=t$12,row(a_rank))) as an array function
  • What does this do?
  • Use indirect to find the name in T14
  • =indirect("a"&t13)
  • Or combine the entire thing in a cell
  • 
• Boolean operators and arrays.
  • It turns out, you shouldn't use logical operations in array formulas.
  • Grab an area and put the values 1:7 in a column
  • In the next column enter =range<=5 as an array formula
  • In the next column enter =range>=3 as an array formula.
  • 
  • 
  • Using a normal formula, build = and(cell1, cell2) as a normal formal and copy it down.
  • Build an array formula with this and as well
  • 
  • Drat.
  • Excel represents true as 1 and false as 0
  • So we can just us * for and and + for or
  • =and(range1*range2) will work just fine.
  • 
  • But I can't find a way to conviently force formatting as bool
  • =if(range1*range2,true,false)
  •