Frequency Function With Criteria

Aug 21, 2007

I have a condition that it only applies to criteria before a certain date but I cannot get it to give me the right answer.

Original Formula ...

View 3 Replies


ADVERTISEMENT

Frequency Function

Sep 15, 2009

My data is on order database as follows:

column B: customer name

column H: order value

column C: Total Order Value for the customer calculated as =SUMIF($B$2:$B$36383,B2,$H$2:$H$36383)

column D: number of orders for that customer, calculated as =COUNTIF($B$2:$B$23155,B2), and defined as a range called No_of_Orders

I'm trying to work out how many customers fall into the various categories. So on sheet 2, I've got an array formula:
{=FREQUENCY(No_of_Orders,$F$4:$F$12)}, where F4:F12 contain the 'bin' values.

The trouble is, that gives me the number of orders that fall into each category, not the number of customers.

How can I get it calculate the number of customers?

View 9 Replies View Related

Frequency With Conditions / Criteria

Mar 6, 2008

I've tried to adapt a formula I've found in the forums. But the result comes back at a figure much lower than I expect.
I have a column of numbers (column J)that I would like the frequency of but only if the date contained in another column (column R) is beyond a certain date.

Columns J & R do contain blank cells.

This is my adaption: -

=COUNT(1/FREQUENCY(
IF('Current Bonds'!$R$13:$R$490>39173,
IF('Current Bonds'!$J$13:$J$490>0,'Current Bonds'!$J$13:$J$490)),
IF('Current Bonds'!$R$13:$R$490>39173,
IF('Current Bonds'!$J$13:$J$490>0,'Current Bonds'!$J$13:$J$490))))

View 8 Replies View Related

Frequency Function And Modifications

Jan 16, 2007

i need to write my own frequency function and after that make some modifications. here is the thing: i dont know how to run over ranges and i dont really know how to start.

FREQUENCY(data_array,bins_array)

Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

View 6 Replies View Related

COUNT Or FREQUENCY With Multiple Criteria

Jan 5, 2009

I have a list of 267 individuals who are each in one of 4 class designations (A,B,C, & D). They produce widgets for me. The individuals’ names are in A2:A268, their class designation is in B2:B268; and the number of widgets they each produced is in C2:C268. Row 1 is a header row.

I used the FREQUENCY function, for the first time, to show how many of the individuals produced 1-10, 11-20, 21-30, 31-40, 41-50, 51-60, and 60+ widgets last year. NOW, I’d like to take it a step further and show the number of individuals that produced widgets in each of these number groupings, but want it further split out by their A, B, C, D, class designation. So, I want to see how many individuals in the “A” class that produced 1-10, 11-20, 21-30, etc. widgets; and how many individuals in the “B” class produced those numbers, etc.

I’ve been playing around with using the COUNT and IF functions together and the FREQUENCY and IF functions, but have not been successful. Am I making this more complicated than it is? By the way, their class designation doesn’t have any relation to the number of widgets they produced, and the data is sorted alphabetically by the individuals’ names.

View 2 Replies View Related

CountIf Frequency Based On 2 Criteria

Mar 26, 2013

I worked out the formula below to count if frequency. However, now I need to work it out for 2 criterias.

Count if I:I = A2 and if R:R = Closed

What I have so far is this:

{=COUNT(IF(FREQUENCY(IF(ticketInformation!I:I=A2,ticketInformation!B:B),ticketInformation!B:B),1))}

View 2 Replies View Related

FREQUENCY Formula With Multiple Criteria

Jan 10, 2008

I hope someone can help me with adding an additional criterion to the formula below, which works and provides an accurate answer. The array formula is:

{SUM(IF(FREQUENCY(IF('Sheet1'!$O$5:$O$315="",IF(Sheet1'!$B$5:$B$315"",MATCH("~"&'Sheet1'!$B$5:$B$315,'Sheet1'!$B$5:$B$315&"",0))),ROW('Sheet1'!$B$5:$B$315)-ROW('Sheet1'!$B$5)+1),1))}

Where Column O has either a date or is blank.
Where Column B has a unique ID number which occurs more than once.

What I need to add is one more criteria from Sheet1,
Where Column P has a unique names which occurs more than once.

I tried two changes:
1. Adding an additional IF statement as follows: SUM(IF(FREQUENCY(IF('Sheet1'!$P$5:$P$315=A$8,IF('Sheet1'!$O$5:$O$315="", ... Which I couldn't get to take; and
2. Adding a IF AND statement as follows: SUM(IF(FREQUENCY(IF(AND('Sheet1'!$P$5:$P$315=A$8,'Sheet1'!$O$5:$O$315=""), ... Which took but gives me a #VALUE error.

View 9 Replies View Related

Count Frequency Of Numbers With Criteria

Jul 10, 2007

I have a column of race results in A:A, ie (1,2,3,4,5 etc placings) and need to count the highest frequency of 'outs' not wins (wins being #1 ) between wins, and also the highest frequency of wins in succession.

View 3 Replies View Related

Function To Calculate Frequency Of Items

Feb 27, 2007

I have a matrix w/ alpha characters in a 10x10 grid. I want to see how many times each letter shows up and then rank them asceding.

What is the function to do this? I looked at Frequency and Count, but do not think either will do the required work.

View 9 Replies View Related

Solver With The Frequency Array Function

Jul 31, 2006

In the attached file I have an example. I've used the Frequency array function to get the distribution of results from some sample result data (50 results). I want to use Solver to change the Bin values to what they would need to be to provide a desired distribution, such as in the example file. I've tried setting the target cell at either the sum of the frequency results or sum of their percentage distribution (100%), and set contraints either frequency results or percentage distribution respectively, along with Bin contraints being consectutively larger (requirement of the Frequency function). Alas, I can't get a solution, only the response of "Solver could not find a feasible solution".

View 4 Replies View Related

Calculating Frequency Of Unique Texts Within Month Under Certain Criteria

Feb 25, 2013

I need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.

I have three columns; Date, Company name and Payment mode.

Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234

View 1 Replies View Related

Sum Function With More Than One Criteria

Dec 6, 2007

I have a large transactions table with 10 columns and more than 7,000 rows. One of the columns names the country in which the transaction has taken place (array named "country")

Another names the year of the transaction (array named "year")
And a third one holds the transaction value. (array named "value")

What I would like to do is to sum the transaction value of one country for one specific year? I.e. what was the aggregate transaction value for transactions in Sweden in 2005?

View 3 Replies View Related

Use Function As A Criteria In A Counfifs?

Sep 15, 2014

I run a countifs against a column of data, and can easily test for specific concrete criteria in other columns, but I want to run a function on the value in a column as a criteria.

For example,
countifs(data Name, "=Technical", data count, ">4")
is great.

But I want to check for some function on another column, e.g. "data number, square(x)<100", I cannot reference the indexed data value in the (data number) column as the "x" in the formula.

What I really want to do (!) is to test if a field is (not) in a list of matches, e.g.
..., data tag, NOT(ISERROR(MATCH(???,GoodValues))),

Where I can see if the tag for an entry is in a list ("GoodValues") defined elsewhere.

View 1 Replies View Related

Use Large Function With Sum Criteria

Aug 24, 2014

I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column

I just attached the sheet : without sorting.xlsx‎

View 5 Replies View Related

Ifs Function Using Multiple Criteria

Dec 15, 2013

I am stuck trying to add another criteria to an existing formula.

On my sheet "Data" i have the following formula in column AF2

=IF(Z2="","A",Z2-AE2)

If Z2 (Payment Date) is Blank then return "A" in AF2, however if Payment Date has an actual date in the cell then minus AE2 (Received Date) from the Payment date.

This gives me the "Receipt to Pay" number E.g. 6 days from receiving to paying an invoice.

I now need to add another criteria to this and i cant fathom it.

I need to do the following:

If Payment Date (Z1) is Blank and Invoice Status (T2) is "Loaded" then Minus Received Date (AE2) from Todays Date, while also retaining the last part of original Formula Z2-AE2 if the first criteria is not met.

(I have put =TODAY() in Cell AG1 for ease).

What do i need to do to add this into the formula. Do i need to use IFS function?

View 3 Replies View Related

Small Function With Criteria

Jan 21, 2014

Criteria:

U4= 12/01/2013

Data:

(A) (B) (C)
01/01/2013 02/01/2013 12/01/2013
1 3 4
2 2 1
4 1 1

Result:

Small - looking for smallest to fifth smallest (separate cells B5=1, B6=2 etc.)

View 1 Replies View Related

If Function With Range And Two Criteria

Apr 15, 2009

I have the following formula below to return "C" if the value in the cell is "C", and nothing if not.

I want to expand it to return "C" if the value in the cell is "C" or "D", for a range between C2:H2.

=IF(C2="C","C","")

I'm have trouble with this easy adjustment.

View 3 Replies View Related

IF Function With Multiple Criteria

Feb 4, 2010

MathScienceLanguageOverall
PassPassPass----
PassFailPass----
PassFailFail----
FailFailFail----

What formula should I use to show "Pass" if a student passed all three subjects, "FAIL" If student did not pass in any subject and "TRY AGAIN" if he passed at least one subject? I can't seem to accurately plot "IF" formula. Should add in "AND" / "OR" as well?

View 5 Replies View Related

Function Using Multiple Criteria

May 5, 2009

Bid Manager Probability of Success Total Fee

cp 25% 675,000
rz 40% 18,383,541
nw 50% 8,700,000
cp 10% 4,500,000
ap 50% 1,200,000
kd 50% 49,500
sl 25% 3,200,000
bc 20% 310,000
sl 20% 3,460,000
nm 30% 6,750,000
at 50% 23,000,000
ap 30% 367,500
rn 40% 13,750,000

I have a list of commodity and percentages of success with fees in the 3rd column
I need a function to count the records with multiple criteria e.g greater than 20% but less than 30%.

Second i need a function when it complies to previous formula it should give fee total pertaining to that count.

View 2 Replies View Related

Offset Function With Criteria

May 22, 2008

I would like a consistent OFFSET function formula across a row but my columns move from monthly to semi-annual periods at a point in time. How do I handle the move from monthly to semi-annual periods in my OFFSET formula?

View 4 Replies View Related

Subtotal Function With Criteria

Jun 21, 2008

I would to use the subtotal command and have it exclude rows from another column in addition to obeying an autofilter:

A B C
1 Area Rep Name Amount
2 Quota John 100
3 NW John 200
4 NW John 300

I want to use the subtotal(9) on the amount column, but I don't want it to include rows where the value Quota is in the area column. The idea is that the amount for a given autofilter condition (rep = john) should only show his forecast amounts but not his quota number for his area. DSUM includes hidden row as does sum

I have tried

subtotal(9,sum(C2:C4*(A2:A4<>"Quota")))

but that does not work

=SUM(C2:C4*(A2:A4<>"Quota"))

works but does not let me filter the data to only show values for john versus all reps

View 5 Replies View Related

Sorting By Frequency

Dec 20, 2008

how can data sorted be more frequency as most frequent on top and as the following:
1
1
1
2
2
3
3
3
3
to
3
3
3....................
i attach file

View 3 Replies View Related

Mode Or Frequency

Jan 31, 2006

I have a large data set. Within the column I have determined the mode. How
do I find the second most common number, third most common, etc.?

240
240
240
240
240
240
240
240
288
288
288
300

View 10 Replies View Related

Frequency Of Numbers

Mar 9, 2006

I have a grid of random numbers. What I want to do is make a new,
corresponding table that has those numbers' frequencies from the initial
dataset.

So, if I have a row 1,2,3,4,5, the first row in my frequency table shows the
frequencies for those numbers, whatever they might be regarding the entire
set of numbers.

I'm just wondering if there's a more automated way of doing this than
running the historgram function and then manually matching the frequencies
with the original numbers to come up with my table. My dataset is growing,
so that's becoming too time-consuming.

View 9 Replies View Related

Formula For Sum Frequency

Apr 14, 2014

I have ID in column A and amounts in column B. ID's are duplicated, and I need to add amounts based on ID, so for example total of ID 156 should be $13,851.14 or 156 should be 7,290.63.

In the following example ID are in order, I need a formula that adds up the amounts in column C.

152
(14,542.81)

152
(199.25)

153
(37,554.98)

153
(19.92)

[Code ...........

View 5 Replies View Related

Sum Character Frequency

May 29, 2008

I have large spreadsheet that analyses the number of times specific characters etc appear in individual cells, but only takes into consideration the filtered cells. My current formula that works fine is:

= sumproduct((subtotal(3,offset($c$66,row($c$66:$c$6959)-min(Row($C$66:$c$6959)),,1))),--isnumber(search("F",$s$66:$s$6959)))

So at the moment every time an F or other specified values is in one of the filtered cells it is counted and displayed in the respective cells. What I would like it to be able to do is recognise when a cell has say 3 F's, and increment the displayed product accordingly.

View 6 Replies View Related

Use Sumifs Function Applying 2 Criteria?

Aug 21, 2013

Excel.xlsx

Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is

=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)

However it is giving a value of zero only. I have tried other combinations as well.

View 4 Replies View Related

SUMIFS Function With OR Statement In One Criteria?

Apr 20, 2014

I need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.

I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.

View 3 Replies View Related

Function To Return Value Based On Two Criteria?

Aug 10, 2014

I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.

In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.

Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".

I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.

View 3 Replies View Related

Adding Third Criteria To Sumproduct Function

Feb 5, 2014

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")

I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.

I tried to edit it myself with the function below but it didn't work.

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")

View 3 Replies View Related







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