Highline Excel Class 38: Array Functions TRANSPOSE FREQUENCY

say I have arrays ArrA and ArrB, of lengths x and y.

I know I can write, say,

Code:

Application.WorksheetFunction.SumProduct(ArrA,ArrB)

to get the sumproduct off the two arrays. however, say i want to sumproduct of just the first z elements of the arrays. is there a neat way of getting this?

I am trying to combine two functions countifs & frequency to no avail. The individual formulas are listed below. To explain the purpose here is a simple example. If there is a diner, I want to know how many people order breakfast in a month on odd days within a price range. I then want to know how many of those people are unique users and not repeat accounts. Any alternative solutions more than welcome. %Right now I am limited to finding all accounts or all counts of instances meeting the criterias.

=COUNTIFS(Sheet2!$E:$E,Sheet1!$B$1,Sheet2!$D:$D,Sheet1!$D$1,Sheet2!$G:$G,">="&Sheet1!$B24,Sheet2!$G: $G,"<"&$C24)

=SUM(IF(FREQUENCY(Sheet2!b:b,Sheet2!b:b!)>0,1))

hi. i wanted to add another IF condition to an existing formula but it's giving me an N/A result.

original formula:

=SUM(IF(FREQUENCY(IF(LEN('Raw Data'!$A$2:$A$864)>0,MATCH('Raw Data'!$A$2:$A$864,'Raw Data'!$A$2:$A$864,0),""),IF(LEN('Raw Data'!$A$2:$A$864)>0,MATCH('Raw Data'!$A$2:$A$864,'Raw Data'!$A$2:$A$864,0),""))>0,1))

i need to add this condition:

IF(LEFT('Raw Data'!$C$2:$C$864,7)="2011-10")

i tried this:

{=SUM(IF(LEFT('Raw Data'!$C$2:$C$864,7)="2011-10",IF(FREQUENCY(IF(LEN('Raw Data'!$A$2:$A$864)>0,MATCH('Raw Data'!$A$2:$A$864,'Raw Data'!$A$2:$A$864,0),""),IF(LEN('Raw Data'!$A$2:$A$864)>0,MATCH('Raw Data'!$A$2:$A$864,'Raw Data'!$A$2:$A$864,0),""))>0,1)))}

no luck. please help. tnx.

How would i write this equation

http://www.datafan.com/Horsepower_Blog/wp-content/TunersHandbook/2-strokefiltered_files/image059.gif

Va = 1100
A = 0.34875
L = 3.937
Vc = 350

Resonant Frequency of flasks = (V/2*3.1415927)*((A/(V*(L+0.5*((3.1415927*A)*2))

=((1100/(2*3.1415927))*(((A/(E353*((N385+0.5)))*((3.1415927*A)*2)))))

as you can see i didn't manage to write it lol

I need the total number of unique orders for each store on a given day. I tried the formula below but I was getting an error.

SUM(IF(FREQUENCY(IF('Raw Data'!$C$2:$C$256="A", IF(LEFT('Raw Data'!$B$2:$B$256,10)+0=A2),MATCH('Raw Data'!$A$2:$A$256,'Raw Data'!$A$2:$A$256,0)),ROW('Raw Data'!$A$2:$A$256)-ROW('Raw Data'!$A$2)+1),1))

Sheet 1

Day (A) Order (B) 2011-01-01 2011-01-02 2011-01-03 2011-01-04 2011-01-05

Raw Data

Order No (A) Order Date (B) Store (C)
Qty (D) 102-765432 2011-01-30T16:35:23+00:00 A 1 103-890123 2011-01-30T03:18:37+00:00 W 1 104-012345 2011-01-29T21:06:33+00:00 W 2 104-012345 2011-01-29T21:06:33+00:00 W 2 104-012345 2011-01-29T21:06:33+00:00 W 2 103-345678 2011-01-29T20:10:18+00:00 A 1 105-123456 2011-01-29T16:31:23+00:00 A 1 102-456789 2011-01-29T12:56:33+00:00 A 1 103-456789 2011-01-29T00:37:08+00:00 A 1 103-012345 2011-01-28T19:15:31+00:00 W 1 104-890123 2011-01-28T18:51:01+00:00 A 1
help anyone? thanks!

-g

I am wondering if there is a way to set up an structure that is similar to an array in Java within excel. I want to be able to use a function to call a specific value out of a list of values. In my mind the equation would look something like =if(a1="yes", ArrayName{2})

Hello I want to populate an array with 2 columns and an unknown number of rows from an existing worksheet. If A:1 + B:1 = 5 then I want the values of the array and the total to be output to a new worksheet in Fields A:1-C:1

I've never worked with arrays in this manor before in this manor and have no idea where to even start.

Thanks for any help!

I can accomplish the same task using a database function (i.e. dsum), a
math function (i.e. sumif), and an array formula. If my goal is to
make the spreadsheet run as efficiently (fast) as possible, which
should I choose? Let's assume this is a very large set of data, and if
pertinent, the equation will be used frequently.

Also, I've heard array formulas in large volumes can slow down a
spreadsheet considerably. Why is this the case? What is the best
source for finding out information like this?

Thanks for your help.

[inputcell: A1] = 1000

[b]....[C]
1000.YES
2000.NO
2001.YES
1000.MAYBE
1000.MAYBE

how do i count how many Yes/No/Maybes there are based on A1?

answer: Yes -1, No-0, Maybe-2 for A1=1000 (these answers can be in 3 diff cells)