Hello,

I am trying to make a comparison of different arrays.

I want to compare customer and supplier specifications. The supplier specification should always be more narrow than the customer specification. And I would like to be able to put text or let the cell blank in the customer specification.

To make it as clear as possible I have taken a very simple system where I compare 2 minimum specification ( I have the same code for the maximum:

under sum, I have put the followings so that I get the number of unconformities: {=SUM(IF(B2:B3>A2:A3;IF(AND(ISNUMBER(B2:B3)=TRUE;B2:B3<>"");1;0);0))}

under array, I have simplified a bit this code to understand what why it did not say one (2<4 so result should be 1): {=IF(B2:B3>A2:A3;IF(AND(ISNUMBER(B2:B3)=TRUE;B2:B3<>"");1;0);0)}

under one-by-one I have put the same system without arrays to better check the problem: =IF(B2>A2;IF(AND(ISNUMBER(B2)=TRUE;B2<>"");1;0);0)

and the same for the next line for A3/B3 (and here I get indeed 1): =IF(B3>A3;IF(AND(ISNUMBER(B3)=TRUE;B3<>"");1;0);0)

My problem is that I do not get the same by working with arrays and without, how should I proceed?! I suppose I misunderstood how to use them...

If it would not be possible to do it this way, can anyone help me about my first problem?!

I have a large amount of data to compare, I need to get the following results for each line of the array separately:

0 if value in B column is a text

0 if value in B column is blank

0 if value in B column is <= value in A column

1 if value in B column is > value in B column

Thanks for your help,

Mosdeb

at very starting level:

=IF(OR(ISTEXT(B1),ISBLANK(B1),B1<=A1),0,IF(B1>A1,1,"NA"))

for array:

=SUM(IF(ISTEXT(B1:B4),0,IF(ISBLANK(B1:B4),0,IF(B1:B4<=A1:A4,0,IF(B1:B4>A1:A4,1,"NA")))))

Press Ctrl+Shift+Enter after pasting in cell. Not just only "Enter"

The array version is differently done because OR/AND is not usable in array. And in non-array is used OR to simplify LOGIC.

A good solution ...Thank you for sharing your experience here.