array formulas

Hi All, I originally posted a question here about Syllogisms (If A = B and B = C then A = C). Here is the Spreadsheet I need help with. !Please note that to reduce calculation time, I’ve removed all but a text version of most of the formulas, which can be found at the top of their respective...

Hi Hope someone can help. I've searched multiple forums I have a table with text entries (e.g., A, B, C, D, E, etc) in a column. In an 'out of the way' part of the spreadsheet against each column value there are a list of values in a row, e.g., for A the values in the row are B, C, D. For B...

Hello, I can't solve this, please help. I have something like this in a single row + + - F8 F8 F12 + - B8 ..... I need a formula which will sum right part of F cells (or B anyway). My columns is like countif for "+" or "-" (solved) countif for "F" and "B" (solved), but I cant sum right...

Hi I have a table (A3:H500) where I have a list of writer's names in column A and each writer's specialties in column H (columns B to G contain personal info that's not relevant for this formula). Examples of specialties in column H are things like 'Australia', 'Travel', 'South Coast', 'Food...

Just looking for opinions... Would an HLookup or a SumIf array be faster?? Thanks!!

Hi I'm using the following SUMPRODUCT formula (array formula) and it works fine: {=SUMPRODUCT(--(RIGHT(Journey!$F$4:$F$1067,3)=F4),Journey!$H$4:$H$1067, SUMIF(OFFSET(SOLD!$J$1,MATCH(LEFT(Journey!$F$4:$F$1067,3),SOLD!$I$2:$I$69,0),),">"&0))} I need to use in in another part of my workbook and...

Hey guys, I am helping a friend with a statistical model and he wanted to know the repeat values. Till now having conditional formatting was enough (highlight the repeat values with simple countif <tbody> =COUNTIF($H7:$M7,B7)>0 </tbody> was serving the purpose but now he wants to do further...

Hello, I need to write a search formula that will search for text in several columns and then return a value in a separate cell. The value returned will be taken from the cell to the left of where the text is discovered. If I use the following table as an example I want to report the price of...

I'm using array formulae throughout my Excel workbook, and I'm working on part of a team. My reporting analyst will at times go through my workbooks to see how it works, but at times will absentmindedly hit 'return' instead of 'esc' when leaving an array formula, which breaks the array and...

Hi All, Had to know if the frequency array function can be applied for a text which we can by using the following formula "=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))" which gives the unique occurrences of text in column A...

Dear Smartest Excelers In The World. I am writing a book for Holy Macro! Books Publishing about Array Formulas. I am making this post in hopes that I could get some feedback about some array formula concepts. Below are five questions that I have for all of you, the smartest Excel people that I...

I routinely need to interrogate large databases to find out if certain core numbers can be found in the databases "catalogue numbers". I have a formula for finding all such occurrences, which is this array formula: {=or(is number(find(lookuparray,database,1)))} This formula will give me a "True"...

Hi, I don't have the Excel skills or knowledge to even attempt this so it will be easier to show the before and after of what I'm trying to achieve. I desperately need to change this source data: <tbody> User Name Entity Code Business Area Level 1 Level 2 Level 3 Level 4 Howard Ratner...

I downloaded a credit-card summary statement. Each row represents a purchase (including date, store name and amount). A given row of data might look like this: <tbody> 1/1/12 Publix #246 SR98286659 $128 </tbody> I want to add a fourth column that automatically qualifies a customized...

Hello everyone, I've got 2 tables Table 1, containing someone's first, second and third language <TABLE style="WIDTH: 204pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=273><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755"...

Hello everyone - I've got the following data: <TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=315><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL...

Hi, I have a set of data. Date........Price........Volume 7am.........99............10 701am......98.............2 702am......98.5..........12 etc. Record set of 46k rows so looking for the most efficient formula. I want to graph the volume shifts but struggling with an efficient formula to...

I am trying to work with a list of data and working out the number of items within that data which tally to set criteria. If I have a list of 10 items which value to 100,000 and I need to look at 80% of this (80,000) and then find the number of items which add up to this value in that range...

Hoping someone might be able to help me! I'm new to array formulas, I got the formula below working. =SUM((Data!C2:C727='Training & PD Analysis'!A9)*(Data!B2:B727='Training & PD Analysis'!J5)*Data!E2:E727) I want to copy the formula down 100 rows. As usual the ranges change as you move the...

Lately I have been replacing a lot of VBA code or complex spreadsheets since I discovered the miracle of pressing CTRL+SHIFT+ENTER. In the example below, I am using the SUM formula to calculate the total Premium where Regions are ON or CC. The problem is that I now have to change the...