|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 8th, 2009, 05:32 PM
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
April 8th, 2009, 05:36 PM
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
April 8th, 2009, 05:46 PM
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
April 8th, 2009, 05:50 PM
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
April 8th, 2009, 06:04 PM
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
April 8th, 2009, 06:47 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
