Compare two rows, send an X

[vici]

Hi all!

Let me describe how the Excel file is written before I get to the problem.

I have three rows, say row 1, 2 and 3.

Row 1: name of all the months and an additional field where it's written "change".
Row 2: numbers for all the months (filled when the year starts)
Row 3: numbers for all the months (empty before the year starts)

The column "change" has two cells merged into one for row 2 and 3.

Now to my problem. I want to compare the numbers in row 2 and 3 for each month, if a change in numbers is detected I want an "X" to pop up in the merged cell saying "change". Eg. if the value for January is 5 in row 1 and 5 in row 2 there is no change and nothing will happen, but if the value would have been 4 in row 2 I want an "X" to pop up where it's says "change".

So I want Excel to compare the values for each month and as soon as one change is detected an X will pop up and stay there, no matter if the change was in January and no other changes exist for the rest of the months.

Values for row 2 is written before the year starts and values for row 3 is added as the months go by, so I need Excel to look month by month starting with January and somehow to know what month it is, eg. if it's April the comparison should only go to MARCH (since the results can be seen one month after) and not further, since there aren't any values for the rest of the year on row 3.

(The values on row 2 is an estimation of how many days a plant will be closed and the values on row 3 is the actual days the plant was closed, which can differ if something unexpected happens. When the plant is on the whole month nothing is written in the cell, not even a zero).

I'm no Excel pro and would have solved it with a lot of IF-arguments, but I need more IF-arguments than what's allowed.

Would appreciate your help! Thanks!

[TMS]

One way:

Cell N2: =IF(SUMPRODUCT(--(B2:M2<>B3:M3))=0,"","X")

Regards

[vici]

Oh, wait, I realised that I forgot to add an important detail of my problem, sorry! Values for row 2 is written before the year starts and values for row 3 is added as the months go by, so I need Excel to look month by month starting with January.

The values on row 2 is an estimation of how many days a plant will be closed and the values on row 3 is the actual days the plant was closed, which can differ if something unexpected happens. When the plant is closed nothing is written in the cell (not even a zero).

I'll add this in the first message I wrote above. Sorry!

[TMS]

Don't change the OP ... it will confuse the thread.

Your best bet is to post a sample workbook with some typical data so that the problem can be seen and addressed in context.

Regards

[TMS]

In the meantime:

=IF(SUMPRODUCT(--(B2:M2<>B3:M3),--(B3:M3<>""))=0,"","X")


Regards

[TMS]

Regards

[vici]

It can look something like this (I dunno how to add a HTML code like you did):

Month Jan Feb Mar Apr May Jun . Jul Aug Sep Okt Nov Dec Change
Estimated 2 5 3
Real 1 5 X

So the plan is to make some stops of the plan in Feb, Jun and Sep: 2 days stop in Feb, 5 days in Jun and 3 days in Sep, but it turned out to be only 1 day of stop in Feb and 5 days in June as planned. But since it's July now, we dunno how many days the plant will be closed this month or the other months of the year, we can therefore not compare the two rows "Estimated" and "Real" from July and onwards. So somehow the formula must consider what month it is now and only compare the two rows until eg. June (because it's July now).

[vici]

Oh no, it looks so bad with the rows, how do I add a HTML code like you did?

[TMS]

Click on "Advanced" and then click on the "chevrons" <>

But, really, you'd be better uploading a sample of your data. Also in Advanced, click on the "paperclip" attachments icon.

But, if you have blanks in row 2 and row 3, use this:

=IF(SUMPRODUCT(--(B2:M2<>B3:M3),--(B2:M2<>""),--(B3:M3<>""))=0,"","X")

Regards

[vici]

Ok, so I uploaded the document.

So first there is an example of what the document might look like before the year starts.

Plant 1 has 2 stop days in April, 16 in July and 2 in August and plant 3 has 9 stop days in July. The fields that are empty means that the plant is on the whole month.

As the months go by it turns out that plant 1 only had one stop day in April (and not two as planned) and plant 3 had one in May, although the plan was not to have any at all in May. There is no deviation from the planned stop days for the rest of the plants.

Since it's July, we can only compare the months until June (we dunno the real values of July yet, since the month is not over), so Excel needs to consider what month it is and compare only till the month before the present month. So there should be an X for plant 1 and 3.

[TMS]

I have updated your example. Looks like we need to go with the second formula.

M5: =IF(SUMPRODUCT(--(C5:N5<>C6:N6),--(C6:N6<>""))=0,"","X")


Regards

[vici]

Hey, thanks! The formula works, but I don't understand why. I understand that SUMPRODUCT will only look at values that differs from eachother, do you think you have time to answer me exactly what the formula calculates, step by step?

[TMS]

You're welcome. Thanks for the rep.

Select one of the cells with the formula in it. Then click Tools | Formula Auditing | Evaluate Formula and then Evaluate.

You'll see that it generates arrays of true and false which it coerces to 1s and 0s and multiplies them together. If the end result is FALSE, you get an "X" in the cell.

So, for example, in cell O5, the formula is comparing C5 to C6, D5 to D6, and so on, generating TRUE or FALSE. It then takes that and checks if the second row in the pair is blank or not..

Regards