Using Range operator (:) In If condition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Soundar - 26 Sep 2007 09:42 GMT

I have an excel sheet, which provided by client, In that one cell contains
formula as follows:
=IF((P42:Q42)=(P43:Q43),"In Balance","Out of Balance")

As per the above formula, My understanding is, P42 should be equal to P43
and Q42 should be equal to Q43, then I will get "In Balance" as output. When
I tried to changes the values in the cells, it is not giving "Out of Balance"
and always showing "In balance" as output. Could you please suggest me on
this.

Reply to this Message

Bob Phillips - 26 Sep 2007 10:41 GMT

=IF(AND(P42=P43,Q42=Q43),"In Balance","Out of Balance")

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have an excel sheet, which provided by client, In that one cell contains
> formula as follows:
[quoted text clipped - 7 lines]
> and always showing "In balance" as output. Could you please suggest me on
> this.

Reply to this Message

alasdair_gifford@hotmail.com - 26 Sep 2007 10:41 GMT

Difficult as you are trying to compare 2 ranges. I would have thought
an array formula exists for this, however arrays are not my specialty.
You colud always just have the following:
Cell P44:
=P42=P43
Cell Q44:
=Q42=P43
Cell R44:
=IF(AND(P44, R44), "In Balance","Out of Balance")

Hope this helps.

Giff

Reply to this Message

Niek Otten - 26 Sep 2007 10:42 GMT

This formula will only compare P42 with P43

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have an excel sheet, which provided by client, In that one cell contains
| formula as follows:
[quoted text clipped - 5 lines]
| and always showing "In balance" as output. Could you please suggest me on
| this.

Reply to this Message

Soundar - 26 Sep 2007 12:16 GMT

Thanks Niek, It is checking 'P' column only and it is not checking 'Q' column
as you said. When I tried by entering value '10' in A1 and A2 and value '20'
in B1 and B2, and used the same formula as follows

=IF((A1:B1)=(A2:B2),"In Balance","Out of Balance")

It is checking for columns 'B' and it is not checking for column 'A'. Is
there any ides, on what basis the formula is deciding the columns, or the
formula which I am using is wrong.

Thanks in Advance,
Soundar.

> This formula will only compare P42 with P43
>
[quoted text clipped - 7 lines]
> | and always showing "In balance" as output. Could you please suggest me on
> | this.

Reply to this Message