I have the following code that I am trying to distribute over an array. The best case scenario is that the code enters the text of the formula instead of the calculated value. When I tried to tweak a few things I couldn't even get the text to show up. This seems to only happen on the formulas which include If_Then statements. Other straight forward formulas such as "sum" etc... seem to go into place and calculate.
Range A1:A10 contains no formulas, only constants.
Only one constant value at most would ever be in that range, sometimes nothing in that range.
The value might be text like "Tom", or it might be a number like "1234".
The value could be in any of the 10 cells.
So far, and this is where the "missing something obvious" part comes in, I have only seen array solutions such as =INDEX(A1:A10,MATCH(TRUE,A1:A10"",0))
My question is, can this result be achieved with a non array / non sumproduct formula.
I need to do this for 6000 cells in a project, that's the design and that's the way it is. I don't want that many arrays in the workbook. I already did this with VBA so I am not asking for a VBA or UDF solution, just hoping to know from the formula wizards what a non-array formula could be that returns the first found (and in this case only-existing) constant value in a range.
However, we have now have two types of deals. Opps and Suspects. SO now i need to figure out the most recent date from Opps!G:G and Suspects!G:G... regardless of what the deal type is. Hope that makes sense.
Basically i need to have a formula along the same lines as above but MAX date from if conditions from Opps OR conditions from Suspects
I know I've asked before but I can neither find my previous question or the answer. So, once again.... is it possible to refer to a named range in a CSE array formula? I know how I would type a formula that way -- but I would be happy to see an example.
Most importantly, will it always work? Or are there pitfalls and dangers?
I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A B C
1 Cat 1 January 1, 2014 John
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example; C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1 C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1 C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
I have a rather large formula that I put together and it's shown in column 'C' in the small sample file I have attached. I'd like to add one more criteria such that I avoid duplicate solutions in column 'C'. i.e. I only want the first occurrence of a group.
The formula works fine 'bucketing' the first entry. If I have an solution in C I want the formula to not give me another answer if the cell immediately above has an answer. In other words, rows 11 and 15 would be blank because rows 10 and 14 have an answer.
I've tried inserting another criteria in the AND statement, like ISBLANK for the cell above, but I can't get it to work. What am I doing wrong, OR is there a simpler solution?
=IF(C2="Windows",B2,IF((AND(A2=A3,C3="Windows")),B3,IF((AND(A3=A4,C4="Windows")),B4,IF((AND(A4=A5,C5="Windows")),B5,IF((AND(A5=A6,C6="Windows")),B6,IF((AND(A6=A7,C7="Windows")),B7,IF((AND(A7=A8,C8="Windows")),B8,"None")))))))Here is a link to the spreadsheet (D2 being the formula cell):
Basically, I am trying to put the columns data (if it has a porch, conservatory, windows) into one row for the contract number, as the dump which I have, is giving me seperate row's instead of one.
I am trying to get the formula to check if the contract number matches on the following row, and if it does, to check if the Product Description matches the header of that row (if it's a window, conservatory) if so, copy the quantity cell into D1, however, it's copying it even if the contract numbers do not match and I do not know where I am going wrong!!
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.
I'm having difficulty creating a nested "if" or range formula that basically only reads the first two characters of the data in a given cell. See attached spreadsheet.
In column "A" I have listed the product ID. If the product ID has the first 2 characters 'DV' then it should yield the result of Dryer. The main issue is creating a formula that only picks up on the first two characters.
Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"
I have 2 worksheets.
Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)
Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg
I have a spreadsheet which contains dates (amongst other info) of when a specific form was completed for a client. We have 'date windows' which these should fall into. I'm really struggling with how to work this one out and whether to use nested IF's or try some kind of code (although my vb knowledge kind of stops after recording a macro). Bascially I need to know for each 'date window' if the window has been met or missed. There may be up to 30 different dates per person (in my example sheet I have up to 9).
I currently use 3 different formulas to return a value I want. The first one is =LEN to return a value. I then want to decuct 1 from this length value so I just use =A2-1 in the next cell. I then use =right so my returned result is always the X right most characters -1. Is there a way I can nest all these formulas so I'm getting the desired result in just one cell?