| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using IF to compare ranges
This seems like it should be a simple task, however, I have entered the
following formula: =IF(A4:I4=K4:S4," ","CHECK") to compare a range of cells to another range (current data vs draft changed data). If there's a difference, I want the word "CHECK" to appear in this cell. In the formula dialog box, the formula result is right, exactly what I want. But when I click OK to apply this formula, the cell in which the formula rests contains the dreaded "#VALUE!" message. Advice?? Thanks, rhodesmk |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using IF to compare ranges
Sorry, I meant to also state that some cells contain numbers, but others
contain text, if that makes a difference. " wrote: This seems like it should be a simple task, however, I have entered the following formula: =IF(A4:I4=K4:S4," ","CHECK") to compare a range of cells to another range (current data vs draft changed data). If there's a difference, I want the word "CHECK" to appear in this cell. In the formula dialog box, the formula result is right, exactly what I want. But when I click OK to apply this formula, the cell in which the formula rests contains the dreaded "#VALUE!" message. Advice?? Thanks, rhodesmk |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using IF to compare ranges
Try,
=IF(SUMPRODUCT(--(A4:I4=K4:S4))=9,"Check","") Mike " wrote: Sorry, I meant to also state that some cells contain numbers, but others contain text, if that makes a difference. " wrote: This seems like it should be a simple task, however, I have entered the following formula: =IF(A4:I4=K4:S4," ","CHECK") to compare a range of cells to another range (current data vs draft changed data). If there's a difference, I want the word "CHECK" to appear in this cell. In the formula dialog box, the formula result is right, exactly what I want. But when I click OK to apply this formula, the cell in which the formula rests contains the dreaded "#VALUE!" message. Advice?? Thanks, rhodesmk |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using IF to compare ranges
Try this array formula** :
=IF(AND(A4:I4=K4:S4),"","Check") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. This does a cell-to-cell comparison. A4=K4 B4=L4 C4=M4 etc etc I4=S4 -- Biff Microsoft Excel MVP " wrote in message ... This seems like it should be a simple task, however, I have entered the following formula: =IF(A4:I4=K4:S4," ","CHECK") to compare a range of cells to another range (current data vs draft changed data). If there's a difference, I want the word "CHECK" to appear in this cell. In the formula dialog box, the formula result is right, exactly what I want. But when I click OK to apply this formula, the cell in which the formula rests contains the dreaded "#VALUE!" message. Advice?? Thanks, rhodesmk |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using IF to compare ranges
Thank you, thank you!
That worked perfectly. "T. Valko" wrote: Try this array formula** : =IF(AND(A4:I4=K4:S4),"","Check") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. This does a cell-to-cell comparison. A4=K4 B4=L4 C4=M4 etc etc I4=S4 -- Biff Microsoft Excel MVP " wrote in message ... This seems like it should be a simple task, however, I have entered the following formula: =IF(A4:I4=K4:S4," ","CHECK") to compare a range of cells to another range (current data vs draft changed data). If there's a difference, I want the word "CHECK" to appear in this cell. In the formula dialog box, the formula result is right, exactly what I want. But when I click OK to apply this formula, the cell in which the formula rests contains the dreaded "#VALUE!" message. Advice?? Thanks, rhodesmk |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using IF to compare ranges
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP " wrote in message ... Thank you, thank you! That worked perfectly. "T. Valko" wrote: Try this array formula** : =IF(AND(A4:I4=K4:S4),"","Check") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. This does a cell-to-cell comparison. A4=K4 B4=L4 C4=M4 etc etc I4=S4 -- Biff Microsoft Excel MVP " wrote in message ... This seems like it should be a simple task, however, I have entered the following formula: =IF(A4:I4=K4:S4," ","CHECK") to compare a range of cells to another range (current data vs draft changed data). If there's a difference, I want the word "CHECK" to appear in this cell. In the formula dialog box, the formula result is right, exactly what I want. But when I click OK to apply this formula, the cell in which the formula rests contains the dreaded "#VALUE!" message. Advice?? Thanks, rhodesmk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare named ranges | Excel Worksheet Functions | |||
| compare date ranges to set up eq | Excel Worksheet Functions | |||
| compare two columns with different ranges in two worksheets | Excel Discussion (Misc queries) | |||
| Compare ranges different size | Excel Worksheet Functions | |||
Linear Mode
