Is there a Function or compound Formula that compares the values of two ranges and returns True/False ?
Something along the lines of : AreRangesIdentical(Range1,Range2) returning True/False.
I have played with some array formulas but they seem to compare the two first cells only.
I know one can easily come up with a UDF that loops through each cell and returns once a cell is found whose counterpart is different but i am looking for a solution that doesn't use a loop.
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
My program assigns a variable one of three values - either "Lancaster", "DeSoto" or "Cedar Hill". My string compares for Lancaster and DeSoto work fine but can't get anything happening for Cedar Hill.
I looked at the value of the variable and it appears to be correct. However, when I did a StrComp for the variable and the string "Cedar Hill", they did, of course, not come out as equal.
The left column is the product, the right the delivery day.
I need to Check and extract each product delivery days. For example, for Q-betacicl I should:
1.- in cell f1, use a Vlookup function to extract 22-jun. thatīs done. 2.- in cell f2, use an If function that compares the cell below Q-betacicl with the cell below. If itīs 0 (like this case) it should extract 01-jul
I used this and excel didnīt even let put the formula, arguing some error I caīt find:
Look for a value equal to E6 (q-betacicl) in matrix A5:b27, and if the cell immediately below it = 0, copy the value to the right of that cell (the one below it). In the case I showed, for Q-BETACICL, it should copy 01-jul. For N-CONTROX it should copy 02-jul.
I am currently trying to take a list of people who have log in times at work however if they log out then back in the result is what you see below.
What I am looking to do is match the find the name then find the earliest time (login Column C) and then the last or latest time (log out Column D)
A B C D John Smith64676076:02 AM6:13 AM John Smith64676077:25 AM11:27 AM John Smith64676212:08 PM4:01 PM John Smith64676076:13 AM7:25 AM
Would like a formula as I have another Tab on the sheet that looks up name and compares against a schedule to see if the person was late or left early.
In cell M32 there will either be an O or the cell will be blank. In cell T51 I had placed an IF statment like this:
=IF(M32="O","","X")
Now that works ok but there is a glitch on my part. If T51 is blank because of M32 containing an "O" then the user will also enter a number into T51. Because I was using an IF formula in the cell it gets overwritten because of the user needing to enter data in the cell because of M32 containing a O. How do I write a macro so that a similar formula is used but still able to enter data manually without overwritting the formula?
I've modified this macro I found on this forum, all it does is compares 2 lists of 6 digit serial numbers and and then tells me which numbers are in list 1 that are not in list 2.
However, I want to modify it so that it also gives me the numbers in list 2 that are not in list 1 (put into column F). I then need it to cut the matching serial numbers (in columns A and B) from sheet 1 and paste them into sheet 2.
I would like to build a graph that compares 2 sets of data but only plots 1 dot where the two intersect. I have seen this graph made by others in Excel but I do not know what it is called or how to make it. An example data set is below.
ID Age Height (cm) 1 5 125 2 7 140 3 8 138 4 11 152
I would just like to plot the location where the age and height for the ID's cross and not 4 plots with the age and 4 plots with the height.
I have a workbook that compares two sheets of data by taking the difference between the two. Some of the cells contain N/A for value, so when I take the difference of two sheets, it gives me #Value! error. My question is how can I format it, so when the cell contains text it displays N/A or if its a value: it takes the difference and populates the value.
I have start dates (column A) and maintenance expiry dates (column B). What I want to do is set up a formula to see if the product was renewed between 01/04/2007 - 31/03/2008 and return a simple yes, no or new_sales.
Examples
If the start date was 26/09/2006 and maintenance expiry date was 26/09/2008 then it would return a yes that they have renewed
If the start date was 18/04/2006 and maintenance expiry date was 18/04/2007 then it would return a no that they have not renewed
If the start date was 20/06/2008 and maintenance expiry date was 20/06/2009 then it would return a new_sale
Hi i have the following SUMPRODUCT formula which works as required, but when i clear the raw data and apply new raw data and apply macro that sorts my data, then all the ranges in the formula shows #REF!
Need a formula for level report. cell c-1 contains percentage for shop production. 100% 105% etc, etc. 100-109=level 1, 109.1-119=level2, and 119.1 and above=level 3. I am in need for a cell to translate the percentage to type 1,2, or 3 accordingly. cell c-1 will recalculate when work production figures change.
I want to freeze the formulas (F4) in whole column in Microsoft Excel, Is it possible to freeze together, not to freeze one by one. I freeze it one by one but that's actually a pain when you have hundreds of formulas to freeze.
I'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.
I need to find a formula which will give me a total of Ģ's between a start and end date.
MY data is an extract with names, individual dates (Ie, 01/05/08, 02/05/08, etc) and costs per day. What i want to do is show a total for the month using the start and end date of that month to add up all the costs within.
The formula below contains an error (one of many as I've been trying to resove this). = COUNTA(IF(CZ4:DE4,DI4:DN4,DR4:DW4,EA4:EF4,EJ4:EO4>"1",Games / Activity,Games)) The formula should return the following result:
If two or more cells in the above ranges contain numbers and cell C4 isn't empty then return the words "Games / Activity", otherwise return "Games". If C4 is empty keep blank. Can this be done with a standard formula?
I have a sheet composed of daily sales. Within this sheet I have averages and totals automatically calculated for each week. I would like to set up a cell that keeps a running total of sales for the entire year as they are entered. I can set up the formulas for each week grouping and they work fine, but I can't seem to get it right for the entire sheet. Here are the formulas:
=sum(b2:g27) =sum(j2:o27)
These formulas work fine by themselves, but when trying to write one formula to calculate the total for both cell ranges I keep getting errors. I just want one formula to calculate the running annual sales as they are entered. I know I'm missing something simple here, but I don't know what it is.
Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.
In column C i have dates. I have formula that count all the dates that are older than 20 days. The formula works perfectly. Now i made a Dynamic Named Range of the column A and now the formula results in #VALUE.
How do I use the SUMIFS formula for ranges greater/less than some number?
In my spreadsheet I need to sum the values that are less or equal to January 1st, for example. How do I do it? Because this formula allow to find values equals to something.
=IF(ISERROR(IF(Search1="","No Place Found",INDEX(LocationsANDLocations2,MATCH("*"&Search1&"*",LocationsANDLocations2,0)))),"No Place Found",IF(Search1="","No Place Found",INDEX(LocationsANDLocations2,MATCH("*"&Search1&"*",LocationsANDLocations2,0))))
But its not finding anything on the second table, being LocationsTable2.
LocationTable is about 65500 rows, and next to it another row of 65500.
What is the best method of linking the them, so it can search for matched text in each cell, so it looks in LocationsTable then LocationsTable2 and so on.
I need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:
User would input the following information:
Zip Code: 56559 Pallets: 3 Weight: 1200
The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".
From Zip Code To Zip Code Pallets Weight From Weight To
I'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet. There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document.
If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc... Here's the code I have developed thus far:
I need to rank the top 3 names for each location based on gender (see sample data table below).
My spreadhseet has been setup using dynamic named ranges ("Size", "Name", "Gender", "Location" and "Score") using the formula "Size"=MATCH("zzzzz",'Sheet1'!$A:$A) and then "Name" =OFFSET('Sheet1'!$A$2,0,0,Size), and so on for "Gender", "Location" and "Score".
Is there a way to design an array rank formula using the dynamic named ranges to return the name associated with the highest score for "M"s in location "N"? I want to be able to edit the formula so I can chose which rank to display (e.g. 3rd, 7th, etc).
I've been playing with the following but can't get it to work
Column A = Date Column B = Reservations made per day
For ex:
A B 1 3/1/2011 5 2 4/5/2011 10 3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2 718-Feb-1124-Feb-11 825-Feb-1103-Mar-11 904-Mar-1110-Mar-11 1011-Mar-1117-Mar-11 1118-Mar-1124-Mar-11 1225-Mar-1131-Mar-11 1301-Apr-1107-Apr-11 1408-Apr-1114-Apr-11 1515-Apr-1121-Apr-11 1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
