Why an "IF-AND" formula does not gives logical result in a matrix?

[guest2013]

Hi,
I'm confused by the fact that an "IF-AND" formula meets logical conditions and produces logical results in certain cells of the matrix while it does not do so in certain other cells.

The example is attached and problemetic cells are highlighted. Can anybody help see what I'm doing wrong?

Best regards

[:) Sixthsense :)]

There is an extra space in G2 Cell data. If you correct it then your formula will show the result.

An alternative approach...

In C12 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

Drag it down and right...

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.

[FDibbins]

Hi and welcome to the forum

I have not yet checked your logic, but I did see that some of your data has trailing spaces? Check these and see if that fixes your problem?
For instance, compare sheet2 F2 and G2 (there are a few others)

[guest2013]

Hi @ Sixthsense
Your array formula is more elegant and effective than I was using.

Thanks a lot for spotting the problem and the bonus solution .

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[ashishraj]

TRY THIS ONE ALSO
=IFERROR(LOOKUP(1,1/((Sheet1!$B$3:$B$10=$B3)*(Sheet1!$C$3:$C$10=C$2)),Sheet1!$D$3:$D$10),0)
this is not array formula

[guest2013]

Thanks @ashishraj. Your solution is also nice.

[Richard Buttrey]

Hi,

Are you sure the answers you've been given give you what you expect. The sum of the numbers in the matrix are different to the sum of your original list. i.e.

original list 0.124
Ashishraj & sixth sense 0.304

Specifically, in the results matrix there are the same count of .017 (2) but an extra 8 of .009 and and an extra 4 of .027.

I only ask since your other thread seemed to imply there should be the same count of the numbers albeit in a different layout.

[:) Sixthsense :)]

@ Richard,

I am unable to follow you please correct me where I am getting failed....

[Richard Buttrey]

@sixthsense.

I think you misunderstand.

I'm not saying you've failed since at the moment it's not entirely clear to me from this and a similar post from guest2013 yesterday, exactly what results he's expecting. That's why I wrote, "Are you sure the answers you've been given give you what you expect".

I'm simply questioning whether the output should contain as many values as there are in the original layout, albeit in a different matrix layout. If not then the answers you've both given are presumably correct as guest 2013 has closed the thread.

Regards

[:) Sixthsense :)]

@ Richard,

Ah! Yes I was little confused that your clarification post was addressed to us (to the Answerers) and now it's clear and thanks for your input

[guest2013]

Hi,

Are you sure the answers you've been given give you what you expect. The sum of the numbers in the matrix are different to the sum of your original list. i.e.

original list 0.124
Ashishraj & sixth sense 0.304

Specifically, in the results matrix there are the same count of .017 (2) but an extra 8 of .009 and and an extra 4 of .027.

I only ask since your other thread seemed to imply there should be the same count of the numbers albeit in a different layout.

Hi Richard,
In fact, all of you have given what I needed and I'm grateful for the help.

Using Ashishraj's formula (I think sixthsense's would do the same) on an edgelist of more than 500 edges, I produced a matrix and then using your proposed method of the other post a matrix of order 153*153 resulted. This is what I needed. A simple test of a random row-column match is attached.

Thanks again for the help.

Cheers,
Guest2013