Data Binning And Array Formula

Apr 5, 2007

see attachment. I would like to calculate the average of the values whose time values fall in between the lower and upper time limits. Or let me rephrase that: If a given amount of time vector entries falls in between the lower limit time vector (at row x) and the upper limit time vector, then I would like to calculate the average of the corresponding value entries. (oh, not only the average but also the standard deviation)

Searching the forum I found a similar entry, but unfortunately Andy Pope's solution attachement seems to be missing: [Solved] Formulas: Counting within bin sizes. I have tried to use the same approach (see attachment) but only came up with counting the frequency instead of calculating the average.

View 4 Replies


ADVERTISEMENT

Binning Data In Excel?

Sep 14, 2011

I have three columns of data, 100,000 plus rows, and I am wondering how would you go about binning the data into say 10 different categories depending on the values in the columns?

Would you use a macro?

View 9 Replies View Related

Binning Function For Datasets?

Jul 24, 2014

I have several data sets taken from an instrument recording the same sample. The data are noisy so I would like to average them. Annoyingly, the operating software for the instrument allows you to specify start and end values, but not the amount of points in between (unbelievable, I know). I therefore have data which looks like:

25, 1
25.1, 5
25.6, 7
25.8, 10
25.9, 12

and

25.1, 1
25.4, 4
25.7, 6
25.9, 14

So each replicate has a different sampling rate (the data set is much larger than the example above though). Is there any built-in Excel function to standardize these data to a fixed amount of x values, and perhaps averaging both the X and Y values of all points within the bin?

View 4 Replies View Related

How To Filter Data By Array Formula

Dec 23, 2013

My Sheet1! looks like (image attached)

On Sheet2! i want to import all rows belongs to Nov-13. Similarly on Sheet3! all rows belongs to Dec-13 ... How to do such dynamic filter using array formula?? I need array as my data source is a form & new rows will be added everyday. I want to do that by formula not using spreadsheets 'Filter" option.

Expected result will looks like below (All Nov-13 rows will return)

View 3 Replies View Related

One Array Formula To Sum Product With Weighting Data

Jun 11, 2014

My problem is a little more complex than sumproduct.

I have a ratings column and exposure column. I want to weight those ratings before doing a sumproduct, at the same time ignoring #value.

In the attached file, I want to achieve the green cell in one go, based on the orange raw data.

Attached File : Weight Data.xlsx‎

View 2 Replies View Related

Array Formula: Add Data Which Meets Certain Conditions

Jan 1, 2009

I'm working with wookbooks used company wide and I cannot add any helper columns which would solve the problem. I need to add data which meets certain conditions see attached workbook for a sample.

View 5 Replies View Related

Using Data From Input Box In Array Formula In Macro

Nov 10, 2011

I often have to research long sheets of data, which requires filling data in on my research sheet from several different sources. The research sheet has a file # in A and a vendor # in C. The data sources have the file #s & vendor #s in varying columns, but I use a pivot table to end up with file # in A, vendor # in B and the needed data in C.

The length of the research sheet varies from month to month - often 800-900 rows, so you can imagine how tedious it is to find the correct file #/vendor # combination to plug the data in. (Many of the rows on the research sheet will not have a row on the data source sheet.)

I managed to get an array formula in one of the columns on the research sheet that actually DOES work:

Code:

{=INDEX('Acct Activity'!C$1:C$7,MATCH(A7&C7,'Acct Activity'!A$1:A$7&'Acct Activity'!B$1:B$7,0))}

But the problem is that it returns #N/A in any row that doesn't have any data. This messes up the sum formula in the farthest right column. So, I tried to incorporate an ISERROR in with the formula, but couldn't get it to work; it left all the cells blank.

In the meantime, I got to thinking that, since there are up to 3 columns requiring data on the research sheet & each column's data comes from a different source, wouldn't it be great to have a macro where I could use a couple of Input Boxes: 1 that would let me click on the column where the data NEEDS to go & 1 that would let me tell it (either by typing the worksheet name or by clicking on the worksheet " PREFERRED " where the data come from)!

So, I spent the entire day yesterday trying to make any of that work & can NOT figure it out! I got the macro to pop up the first input box, & can key in the column letter, but clicking OK doesn't do anything. Yesterday, I had it so I could click OK & the box would go away, but nothing else happened & the second box wouldn't work right.

THEN, I started trying to figure out how to do an array formula in a macro & my head nearly exploded! I have a terrible time trying to understand written descriptions if they're in "tech speak", so it was all greek to me.

Here is all the farther I got with the macro:

Code:

Sub ClearingRsch()
' Jenny 10092011
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False

[Code] ......

View 9 Replies View Related

Data Validation Cell Not Recognized In Array Formula

Nov 14, 2013

I am trying to get my array formula to recognize the text selected in my data validation cell. The formula worked perfectly until I decided I wanted to try and use a data validation list to eliminate spelling errors. Even though the text I am trying to recognize in the cell is exactly the same, because it is in a data validation format it no longer works. Is there a way I can modify my formula so it works? Example below...

formula: {=SUM((A2:A109>DATE(2013,10,31))*(H2:H108="Joe Blow"))}

A2: 11/2/2013
H2: Joe Blow (chosen from a data validationi list)

The result of the formula should equal 1 in this example, or more if the same criteria occurs more than once within the range of A2:A109, and H2:H109.

View 7 Replies View Related

Array Formula: How To Aggegrate Data, Removing Blank Cells

Aug 29, 2007

Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.

ie:
------------
column1
------------
(blank)
1/1/2007
(blank)
(blank)
(blank)
2/5/2007
(blank)
(blank)
3/7/2007
(blank)
... etc ...

I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.

so the new column would be:

--------
Column2
--------
1/1/2007
2/5/2007
3/7/2007

I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.

=ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))

View 10 Replies View Related

Wrong Data Type Error In Simple Array Formula

Jun 30, 2013

I am trying to use FIND and an array formula to find the position of text in a range of cells (A2 and A3 in the example) which could be one of a number of options (C1:D1 here). But the array formula throws up the following error: "A value used in the formula is of the wrong data type". The simplest illustration of the problem is as follows. The formula in B2 is

Code:
{=FIND(($C$1:$D$1),A2)}
and $C$1:$D$1 contain REF and ATM respectively. [/CODE]
REF
ATM
203047 05AUG 08.55 OKEHAMPTON ATM
#VALUE!
CO-OP GROUP 380611 REF 191 7553375222 BCC
22

We see that B2 has a #VALUE! error - wrong data type. But for some reason B3 is ok returning 22!

View 9 Replies View Related

Converting 3x10 Array To A 1X30 Array To Run A Match Formula

Apr 7, 2009

Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

View 6 Replies View Related

Index Match Array New Formula Not Array?

Nov 6, 2013

Is there anyway to recreate this formula w/o it being an array ?

{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}

View 5 Replies View Related

Concatenate An Array (using Array Formula)

May 18, 2007

I just wanted to concatenate a few cells using an array formula like this:

{=SUM(IF(B2:J2="";0;B2:J2))}

Well, this does not work. I have no idea why it fails. Any solution to this without scripting?

View 9 Replies View Related

Using Array Variable Instead Of Array Formula

Jun 30, 2006

i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example

View 4 Replies View Related

"You Cannot Change Part Of An Array" When Editing Array Formula

Aug 25, 2006

I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"

=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))

View 5 Replies View Related

Looking Up Row Array Data In Column Array

Jul 11, 2007

On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8

What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want

=sumif(A84:A89;"x";'X-ref'!T4:Y4)

but it doesn't work since the first range is an column-array and the second range is an row-array.

View 5 Replies View Related

Can A Macro Write A Formula As An Array Formula

Jun 7, 2006

I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?

View 2 Replies View Related

Using An Array Formula To Sum

Mar 6, 2007

I want to use an array formula to sum about 5 rows each of which has 5 columns, e.g.

1 2 3 4 5 15
2 3 4 5 9 23
5 4 3 2 5 19
9 8 7 6 5 35
7 6 4 2 1 20

The final column is the total for each row. Instead of using =sum(a1:a5) then dragging the formula down thus creating a new formula for each line, I want to use a single array formula to achieve the same result. I have tried =sum($a$1:$a$5:$e$1:$e$5), even though all my instincts said it did not look right. And I was right!

I'm sure it is easy, but I just cannot work out the syntax.

View 11 Replies View Related

Array Formula In V.B.A.

Aug 2, 2007

I have an formula for calculating the factor of a number this a fairly long array formula I decided to try to write this in vba to use as a user defined function. I first decided to record a macro of me writing the formula in excel to see what the code looked like, this I then adapted in another module copying the basic formula in a function procedure. my recorded macro works fine when I run it but my adapted function shows an error namely that the FormulaArray is not defined. I've tried all kinds of variations but with no success. how do I write an array formula in V.B.A.

View 13 Replies View Related

Array Formula

Oct 16, 2005

I have a list in excel that contains the same sku more than once on a
workbook in no paticular order, however i want to bring back the last
time that this sku appeared on the workbook for example:

SKU 271562 date counted 11 september
SKU 275555 date counted 12 september
SKU 271562 date counted 12 september
sku 255212 date counted 13 september
& so on
SKU 271562 date counted 11 october

The date range is only applicable to the date counted therefore i
cannot search via a date

2. vezerid Oct 13, 7:09 pm show options

Newsgroups: microsoft.public.excel.worksheet.functions
From: "vezerid" <veze...@act.edu> - Find messages by this author
Date: 13 Oct 2005 11:09:27 -0700
Local: Thurs, Oct 13 2005 7:09 pm
Subject: Re: Excel help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

View 14 Replies View Related

IF & MAX Formula With Array

Aug 27, 2009

The following formula works, but takes far too long for an efficient user to run (note the array):

{=(MAX(IF(($B$4:$B$50000=$B4)*($I$4:$I$50000=$X$1),1*$J$4:$J$50000)))}

B = Number Values (e.g. 3431003)
I = Simple Text Values (e.g. I, P)
J = Dates (e.g. 4/15/2009)

Is there a formula that would provide the same result in a quicker fashion?

Note: Unfortunatly, the data will get up to 50,000 rows.

View 12 Replies View Related

3D Array Formula

May 9, 2007

is it possible for an Array formula to be 3D? I am having no success, and I have heard it is - if you know how to do it right...

View 9 Replies View Related

Can Formula Be Placed In An Array I.e. { } ?

Jun 1, 2007

Seems not.

For example,

=SUMPRODUCT({1,0,1,0},F24:F27)

is ok. But

=SUMPRODUCT({if(F24=1,1,0),0,1,0},F24:F27)

is not.

Why is this so or is there a way to go aroung this?

View 9 Replies View Related

Array Formula In VBA

Aug 25, 2009

I have a lengthy array formula that I try to enter using VBA. I get the common run-time error. I tried breaking it down, but that did not work. Maybe the syntax was wrong in the break down. Can some help out please?

=IF(COUNTIF($B:$B,B2) 1 Then
Range("B" & x).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True

View 9 Replies View Related

Using A UDF In An Array Formula

Aug 28, 2009

I have a custom UDF (user defined function) that I have been using for about 2 years with success. I am now trying to use it in an array formula and getting a #Value! error.

The UDF looks like this...

=MyUDF(A1,B1)

...where A1 is a string to be evaluated and B1 is a number. Basically, the function returns another number depending on the relationship of the two inputs. I have not had any issues in the past using it as shown above.

Now, I would like to use this function on a range of cells and sum the results. Here is what I have now...

{=SUM(MyUDF(A1,B1:B100))}

Does anyone know why this returns a #Value! error?

View 9 Replies View Related

Stuck With Array Formula?

Jan 19, 2014

I am using the following array formula "=SUM($I$35:$I$1000*($G$35:$G$1000=$B$10))" which works fine but if there is nothing to sum it returns "0", but I would prefer a blank cell if there is nothing to sum.

View 3 Replies View Related

How To Create Array Formula

May 24, 2014

I have attached a spreadsheet of data that I would like to reduce.

In the workbook there is a set of data (rows 1 - 24) which are defined with random dates (row 2) and number criteria column A.

In the table below that (rows 28 to 37) I have set out how I would like to present the data. how to create an array formula that sums the above data months into the criteria of column A.

View 5 Replies View Related

How To Exclude Row From Array Which Has Formula In It

Jul 30, 2014

For example the formula below is in cell AU6. I want to exclude A6 from the array. Row AU7 would need to exclude A7 when I drag it down.

=MAX(IF($A$6:$A$493=AT6,$AS$6:$AS$493))

View 3 Replies View Related

Index Or Array Formula..?

Dec 2, 2008

I am trying a simple Index or Array formula and have been trying all day to get this work to no avail. The data is freight costs for shipping different size containers (20' and 40') to different ports (Lyndhurst, Port Coquitlam and Seattle).

I will have the port name plugged into one cell and the container size plugged into another cell and want to pull the corresponding freight cost out into another cell. The red text in the file are the data that I will plug in. The box below is where I would like the result.

View 5 Replies View Related

Array Formula - St Deviation

Mar 2, 2009

I have a list of 187 countries and I would like to find a way to do this with an array and not by hand. Attached here is an excel with a sample; I would like to compute the standard deviation of the variable for the period 1980-2008 for each country separately. Is there a way to do this without me having to select by hand the range corresponding to each country in the st dev formula?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved