If statement

[nietor]

Hello,

I am unsure if the if function is the correct one to use, i've been searching on the internet for a while now and i cant figure out how to do the following:

On my worksheet i want to verify that three conditions are met, and if they are for "anulled" to show in a specific cell, i know this is done with a nested if function however, i find it difficult to express in excel the conditions that i want to verify.

Basically i want to search first within a range of numbers for the opposite of a number, so if lets say in cell A1 the value is 150 i want to see if in column A the number -150, exists, and then verify if the number in cell B1 coincides with the number for the value corresponding "-150" in the same column, following this i would like to verify the same with another column. If the three conditions are met i would like for it to display in a cell "annulled", if not just a blank.

How may i go about doing this?

Thanks in advance for your time, and i hope i was clear enough in my explanation!

[Richard Buttrey]

Hi,

Can you upload an example workbook showing your data and a before and after situation, noting if it's not obvious the logic you used to arrive at your 'after' position.

Rgds

[NBVC]

Something like this maybe:

=IF(Sumproduct(--($A$1:$A$100=-$A1),--($B$1:$B$100=$B1),--($C$1:$C$100=$C1)),"Annulled","")

where --($C$1:$C$100=$C1) is the third comparison.

[nietor]

Thanks for the prompt replies, attached you'll find what im working on.

Looking at the example:

I want to basically see which entries cancel out with each other. So i would need to look for opposite number matches in column J. And verify that column D and A coincide in value for the corresponding number matches (like the ones highlighted in yellow).

Hope this helps to clarify.

Thanks again!

[NBVC]

Do the dates in H and I need to match?

[nietor]

No, that is not necessary, basically A is a main category, D is a subcategory and the J is value

[NBVC]

How do you know then which 2 to colour.. are the +/ve and -/ve necessarily consecutively listed? Using my formula above, for example will highlight 3 consecutive cells, +/ve, +/ve, -/ve... I guess you want only the last 2... will they be consecutive?

[nietor]

It doesnt really matter for the purpose i need to show that they are anulled, if they are specifically the same date or not... i couldve highlighted the other one instead, as long as the values cancel each other out and correspond in the A and D columns it is fine. It doesnt have to be highlighted either thats just for easy visualization. Also, they will normally not be consecutively listed, in the example i had previously sorted them, because im having to do this verification manually for spreadsheets with over 8000 columns but it takes too much time in my opinion to sweep through them manually.

[NBVC]

Try in L4:

Please Login or Register  to view this content.

copied down...

[nietor]

Thanks a lot!! The formula works great from what i can test, however, when i transferred it to the bigger excel worksheet it gave me an #value! error, even after i modified the letters in the function to the corresponding ones. Is this related to position specific fields in the formula?

Thanks again!

[NBVC]

If you adjusted the ranges/references properly should be okay...

Are there any errors displayed in any of the range columns? Or is there any text in column J within your set range?

[nietor]

Yeah, apparently i had made an error when changing the ranges, i am going to verify with a spreadsheet that i have already manually verified, and i'll let you know if it works 100% asap. Thanks again!

[nietor]

Ah yes, there are some blank sectors, wehre no information is available for some of the range columns, you can take a look at the full spreadsheet i upload with this post. Sorry i cant send it with complete information due to confidentiality issues, but the relevant data is there, and the columns.

Thanks

[NBVC]

Try:

Please Login or Register  to view this content.

in Row 2, copied down...

[nietor]

thanks again!