Comparison Formula - Including Inherent Errors

Oct 5, 2009

I have two columns that I want to compare - but one of them is the result of a VLOOKUP - so it may actually contain the #N/A error - how can I compare them so that:

if col 1 agrees with col 2, then "yes" is returned
if col 1 does not agree with col 2 but the VLOOKUP in col 2 has NOT returned an error (so it's still a valid comparison) then "NO" is returned
if col 2 shows a VLOOKUP #N/A error then "NO" is returned

or, perhaps more straightforwardly:

Col 1 = ABC, col 2 = ABC then return "YES"
Col 1 = ABC, col 2 = DEF then return "NO"
Col 1 = ABC, col 2 = #N/A then return "NO"

View 4 Replies


ADVERTISEMENT

Formula For Comparison Of Data

Jul 28, 2009

I have a spreadsheet and I'm comparing data from a room booking system and a spot check on the room to see whether it's actually used. I have a column which I want to show whether the information agrees.

column G - I have a group size recorded that should be in a room.
column H - I have "yes" or "no" as to whether there was anyone in the room when it was checked
column I - I have the size of the actual group in the room.

Is it possible to create a formula that will fill in column J with the answer yes or no depending on whether it agrees.

It doesn't matter whether the groups sizes match, it's just a case of yes, someone was booked into the room and there was someone in when it was checked

OR

No, there was a booking supposed to be in the room but there was no one in it when checked

OR

There was no booking but there was someone in the room when checked.

View 4 Replies View Related

COUNTIF Formula - Time Comparison

Sep 28, 2012

I am putting together an email reporting template in Excel that pulls in data from various sheets. The reason I'm doing this is because we need to brand the reports as our own, but our email service provider doesn't offer this capability. As a result, I have the report template saved on one worksheet, and I have copied and pasted other bits of the report that I need onto other sheets.

I need to create an area graph of the number of recipient opens against time (a response graph basically). So along the X axis I will have time periods (0-1 hours, 1-2 Hours up to 1-2 Days, 2-3 Days etc). Along the Y axis will be the number of recipients who opened the email within that particular timeframe. I can produce the graph easily enough when the data is in front of me, however, getting the data in the right format is proving tricky.

The list of open times is stored in column 'D' on a sheet called 'OPEN TIMES'. The reference time (time the email was sent) is stored in cell 'A60' of sheet "Full Report". As I see it, I should be able to use a COUNTIF formula to total the number of opens that meet a criteria (within a certain timeframe). So, this is the formula I tried:

=COUNTIF('Open Times'!D:D,('Open Times'!D:D

View 9 Replies View Related

Complex Multiple Cells Comparison Formula

Jan 16, 2014

I've tried some of the basic formulas, but this is beyond me.

I have table A of 500 rows and need to compare it to table B of 2000 rows.

A
B
C
D
E

1
D6 - 1.01
Table with PC
3
Yes

2
D6 - 1.01
Bookshelf
4
No

[Code] .......

For example with these two, from the second table to the first. IF column B equal to column and the corresponding column C equal to the corresponding column C, return for example "yes". Then I need to check the same thing for the corresponding other columns, but basically everything would be tied to B and C. The problem is the second table, larger one, is basically all over the place, I could manage to do this if we would be talking equal length tables, but I do not know what formulas I could use to check further down a row once we find an equal value.

View 5 Replies View Related

Comparison Formula To Return Difference In Quantity

Nov 13, 2013

i am comparing 2 inventories, they contain the same information. I have included these on 1 sheet

Column A: Part numnber Inventory 1
Column B: Stock number Inventory 1
Column C: Quantity Inventory 1

Column E: Part numnber Inventory 2
Column F: Stock number Inventory 2
Column G: Quantity Inventory 2

I need a formula that says: If column B = column F, return the difference, if any, of Coulmn C - Column G to Column H

The data is not going to be inline, so B1 is not necessarily going to = F1

View 2 Replies View Related

Simple Date Comparison Formula - When Two Dates Are Equal To Each Other

Feb 10, 2014

Report I generate once a month that checks to see if tasks are done on or before their due date. Sometimes it works, and others it doesn't...

The basic formula is: [Code] .....

If they closed their task by or before the due date, the condition should be true and it should output "Compliant" beside their task. Otherwise their "Over Due".

The forumla works except when they close their task ON the due date. No matter how I tweak the forumla, it thinks the condition is false as if it cannot tell when the two dates match.

View 1 Replies View Related

Excel 2003 :: SUMPRODUCT Date Comparison Formula

May 7, 2014

The following formula works perfectly in Excel 2003:

=SUMPRODUCT((A2:A10="A")*(b2:b10="B")*(c2:c10="C"))

However, I've got a fourth column that contains either a date or a blank cells; I want to limit the formula to only include certain dates in the sum. i.e:

=SUMPRODUCT((A2:A1000="A")*(b2:b10="B")*(c2:c10="C")*(d2:d10>="01/04/2014"))

Rather than an error it's just retuning an incorrect number.

View 6 Replies View Related

Including 0 In Formula (#DIV/0!)

Jan 31, 2010

I am trying to create a marksbook that will ignore blank cells and calculate a percentage based on the amount of tasks completed.

So far I have managed to get it all working EXCEPT if I enter 0 as a value.

For example, suppose there are two tasks, both of equal value. If a person was to get 100% for the first task, and 0 for the second task, you would expect the total to be 50%. I cannot get my formula to recognise that a 0 has been entered.

(Cells F5:T5 are what the task is out of. Cells F6:T6 is the weighting. I want to be able to enter a 0 in cells F8:T8 to include it in the final percentage.) ...

View 9 Replies View Related

Avoiding #value! Errors In My Formula

Nov 17, 2009

I have the formula =today()-g3 which works fine to let me know how many days a loan has been out. However when there is no date in column g i get #value! errors. Can anyone reccomend an alteration to the formula to avoid these errors showing up as they dont look great.

View 2 Replies View Related

Formula Errors In Some Cells

Feb 17, 2014

The following formulas is showing error in some cells?

=IF(ISTEXT(PRONOSTICOS!G5), PRONOSTICOS!G5, "")

View 3 Replies View Related

Formula To Find Errors

Jan 15, 2010

I need a formula that will check a few things and if it finds an error the word error will appear in the cell, the details of which are in the attached spreadsheet.

I have worked on this a great deal and cannot seem to make a formula with the proper logic. There is a sample of an error in the spreadsheet.

View 4 Replies View Related

Including Worksheet Tab In Formula

Jul 8, 2013

I have a workbook with numerous worksheets. Each worksheet is similar to all the others, and I want to be able to (for example) lookup the values in cell C10 for each worksheet and record the value in a summary worksheet.

The name of each worksheet is in Row 1. Is there a way to reference the sheet name using the values in row 1?

View 3 Replies View Related

SUMIF Formula Including OR

Jan 30, 2009

How do I modify my formula (below) to include an OR statement?

=SUMIF(I4:I20,"Core",G4:G20)

I need something like this:

=SUMIF(I4:I20,"Core" or "Existing",G4:G20)

View 9 Replies View Related

Hiding Formula Errors

Oct 6, 2004

Is there a way of hiding the #N/A which is returned as a result of a formula, but keep the formula in a cell so it may be included in a sum?

View 4 Replies View Related

Replace Formula Errors With Zero

Nov 8, 2006

how i can search #N/A , #values error in a work sheet and replace with 0. i tried with find and replace dialague but it seems not recognizing error values.

View 2 Replies View Related

Averageifs Formula With And / Or Criteria Including Odd / Even

Feb 7, 2014

I have 3 columns of information. Column A will be between 1-6, Column B is between 1-10, Column C is the results that I need averaged. I need the formula to give me the average of Column C of all 1's in Column B that are even numbers in Column A. I have been looking on forums, and can find multiple criteria for ands, but can't see where I can do an and /or. I could do Column B = 1, Column A =2, but I need it to be OR =4 OR =6. I don't see any easy answer in excel for even/odds.

View 8 Replies View Related

Ignoring Errors In DAVERAGE Formula

Jun 10, 2014

how to ignore errors when using DAVERAGE?

View 11 Replies View Related

Including Destination Cell Value In A Formula

Mar 28, 2009

There is a big range of cells with normal numbers (ex. 100, 150 .. etc), but I need to convert them in the following formulas that give the same numbers as a result: for example if the cell value is 100, I need to convert it in =if(iserror(100);0;100) and so on with all other values. Is it possible this to be done automatically for all cells?

View 5 Replies View Related

Catch/Trap Formula Errors

Sep 15, 2006

I am trying to find some solution on frequent errors , that come up when one copy, or drag formulae.
Sometime the error doesn’t appear on surface, but after digging you realize that some formula is not pointing to correct
cells.

I want to create a macro which will do following things :

1. It will go to each cell in the selected range, and compare the cell’s formula with that of the cell on its right and left.
2. If the formulae are not similar it should change the color of that cell.

Basically, I am trying to catch logical errors. However I don’t know how to approach this problem? Is there any in-built functionality in Excel that can do this task ?

View 9 Replies View Related

Excel 2002 :: Formula To Display All Errors?

May 27, 2012

In a worksheet the data (text or numerical) from five columns are compared using the following formula.

=IF($A10$G10,"1ERROR",IF($B10$H10,"2ERROR",IF($C10$I10,"3ERROR",IF($D10$J10,"4ERROR",IF($E10$K10,"5ERROR","-")))))

The formula is working well. The problem is I get the result for the first column of error even if the error is in more than one column. I want the result should display all the errors with the column number 1, 2, 3 (or with column name A, B, C etc). The total number of error is not required. The exact columns which have the error should be displayed. For example if the error exists in 3, 4 and 5 columns, I should get the result as "345Error" or "CDEError".I use excel 2002.

View 6 Replies View Related

Formula To Show Top Scores - Including Ties

Jul 17, 2008

I have a list of team members and their scores for team building events. I need to create a list of the top ten scores (including any ties). The list will have to show the top ten scores as well as any additional names whose score ties with one of the top ten scores.

I tried large and also a pivot table using the advanced but cannot get it to show the top 10 unique scores as well as any duplicate scores.

View 9 Replies View Related

Identify Formula Errors Macro Code

Mar 6, 2008

i would like an if macro to pick up if cell dest (i have used a case to define this cell) contains an error or more imoprtantly #REF! then change the offending cells to 0 and put up a message box to put "Check XTA". i have found some that i think may work but i didnt understand them (they had function in them :smask so i couldnt put them in.is there a way to put them in with out functions or could someone point me in the right direction.

View 4 Replies View Related

Formula To Work Out Cost Including Multiple Discounts

Mar 31, 2014

complicated formula for working out prices including applying discounts.

I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19

I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.

View 2 Replies View Related

IF Formula - Get Average Of Only Values In That Range Not Including Blanks

Nov 21, 2011

I am looking for a formula for the following:

If there is nothing is U6:W6 return blank

Otherwise sum the contents of Bf6:BH6 and divide by the number of cells that are not empty in the range U6:W6 (to get an average of only the values in that range not including blanks).

View 3 Replies View Related

Code Errors On Setting Cell Values To A Formula?

Jul 15, 2014

the following two statements return the error "Application-defined or object-defined error"

Code:
Sheets("Purchase").Range("PurchaseTax").FormulaR1C1 = "=IF(RC[-1]0,ROUND(RC[-1]/11,2),"")"
Sheets("Purchase").Range("FreightTax").Formula = "=IF(FreightCharge0,ROUND(FreightCharge/11,2),"")"

View 2 Replies View Related

Formula To Return Multiple Criteria Including Stretch Target

Jul 8, 2014

I'm struggling to come up with a formula that ill give me the following:

I have will have a row of data (cells A4:H4 on the attached example), in the respective cells below each value in row 4 I want formula that will give me a value of 10 if it sees 90% in the cell above reducing to a value of 0 if it sees 80% in the cell above. I think that there should be a relatively straightforward formulae for that, my problem is that I want to add a further criteria that says; if the value in row 4 increases above 90% and reaches 95% then I must return a value that is 10 for 90% but "stretches" to 12 for 95%. Anything below 80% should return a value of 0, and anything over 95% should return a value of 12.

View 8 Replies View Related

Formula To Calculate Result Whilst Avoiding Errors Is Too Long!

Jan 22, 2010

I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.

I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:

View 3 Replies View Related

Date Formula With Variable Date & Comparison Operator

May 14, 2008

I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?

=SUMPRODUCT(--(wc_date_of_loss&A3))
=SUMPRODUCT(--(wc_date_of_loss=A3))
=SUMPRODUCT(--(wc_date_of_loss=TEXT(A2&A1,"general,dd/mm/yyyy")))

View 6 Replies View Related

Formula For Obtaining List Of Unique Values But Not Including Values Which Contain IC

Aug 14, 2014

1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).

I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)

2. To include all unique string values except those starting with the letters "IC"

3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.

Here is the formula I currently use which serves #1 (above):

[Code] .....

Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.

Example of desired results:

Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |

View 14 Replies View Related

Comparison Of The Two Lines?

Feb 19, 2014

I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :

7,7,K,A,8,7
K,6,7,7,A,9

To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))

The result is 4

Is there a single formula that will give the result ?

Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?

View 8 Replies View Related







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