Hi,

I have a WB, with 3 WSs in it.
Let's call them, A,B,C

The numbers in C are given by A - B
=IF(ISERROR('A-'B),0,'A-B)

The problem is that if I have no data in B then I get A numbers into C

How can I add a third condition to the formula so that when no data are
present in B then it should show Nothing in C?

Thank you!
Alex

Hi

P.e. with formula on row 2
=IF(B2="","",(B2<"")*SUM(A2,-B2))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Metallo" wrote in message
...
Hi,

I have a WB, with 3 WSs in it.
Let's call them, A,B,C

The numbers in C are given by A - B
=IF(ISERROR('A-'B),0,'A-B)

The problem is that if I have no data in B then I get A numbers into C

How can I add a third condition to the formula so that when no data are
present in B then it should show Nothing in C?

Thank you!
Alex

Hi Arvi,

Could you please explain abit the formula?

This is what I have as a real example:

=IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to
2003'!G9)

I should I change this it?

Thank you
Alex

"Arvi Laanemets" wrote:

Hi

P.e. with formula on row 2
=IF(B2="","",(B2<"")*SUM(A2,-B2))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Metallo" wrote in message
...
Hi,

I have a WB, with 3 WSs in it.
Let's call them, A,B,C

The numbers in C are given by A - B
=IF(ISERROR('A-'B),0,'A-B)

The problem is that if I have no data in B then I get A numbers into C

How can I add a third condition to the formula so that when no data are
present in B then it should show Nothing in C?

Thank you!
Alex

Hi

=IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp
to 2003'!G9))

(there was an error in previous formula, it had to be
'=IF(A2="","",(B2<"")*SUM(A2,-B2))')

What does the formula do?

IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is
empty, nothing is returned. In case you wanted nothing to be returned with
cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be:
=IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp
to 2003'!G9))

You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is
empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which
in Excel are interpreted as 1 or 0 , when multiplied with (or added to)
numeric values. So whatever value the SUM returns, when there is no entry in
cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the
sum is multiplied with 1, and the sum is returned.

Now the last part of formula. You used ISERROR to check for empty cells,
because mathematical operators don't work with empty cells or with cells
containing strings. But SUM function simply ignores such cells. So when cell
'2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell
'2003'!G9 is summed. No errors anymore.


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Metallo" wrote in message
...
Hi Arvi,

Could you please explain abit the formula?

This is what I have as a real example:

=IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to
2003'!G9)

I should I change this it?

Thank you
Alex

"Arvi Laanemets" wrote:

Hi

P.e. with formula on row 2
=IF(B2="","",(B2<"")*SUM(A2,-B2))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Metallo" wrote in message
...
Hi,

I have a WB, with 3 WSs in it.
Let's call them, A,B,C

The numbers in C are given by A - B
=IF(ISERROR('A-'B),0,'A-B)

The problem is that if I have no data in B then I get A numbers into C

How can I add a third condition to the formula so that when no data
are
present in B then it should show Nothing in C?

Thank you!
Alex

Arvi,

I tried both the formulas, but they still return the value which is in
'2003'!G9, while I would expect nothing (0).

Can you re-check it?

Thank you
Alex

"Arvi Laanemets" wrote:

Hi

=IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp
to 2003'!G9))

(there was an error in previous formula, it had to be
'=IF(A2="","",(B2<"")*SUM(A2,-B2))')

What does the formula do?

IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is
empty, nothing is returned. In case you wanted nothing to be returned with
cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be:
=IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp
to 2003'!G9))

You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is
empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which
in Excel are interpreted as 1 or 0 , when multiplied with (or added to)
numeric values. So whatever value the SUM returns, when there is no entry in
cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the
sum is multiplied with 1, and the sum is returned.

Now the last part of formula. You used ISERROR to check for empty cells,
because mathematical operators don't work with empty cells or with cells
containing strings. But SUM function simply ignores such cells. So when cell
'2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell
'2003'!G9 is summed. No errors anymore.


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Metallo" wrote in message
...
Hi Arvi,

Could you please explain abit the formula?

This is what I have as a real example:

=IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to
2003'!G9)

I should I change this it?

Thank you
Alex

"Arvi Laanemets" wrote:

Hi

P.e. with formula on row 2
=IF(B2="","",(B2<"")*SUM(A2,-B2))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Metallo" wrote in message
...
Hi,

I have a WB, with 3 WSs in it.
Let's call them, A,B,C

The numbers in C are given by A - B
=IF(ISERROR('A-'B),0,'A-B)

The problem is that if I have no data in B then I get A numbers into C

How can I add a third condition to the formula so that when no data
are
present in B then it should show Nothing in C?

Thank you!
Alex

Arvi,

Probably I omitted an important information, '2005 Comp to 2003'!G9 is empty
in terms that there is no value in it, but that cell contains a formula which
is supposed to return a value in case a number in input in another Sheet.
Could this be the reason?

Thank you
Alex

"Metallo" wrote:

Arvi,

I tried both the formulas, but they still return the value which is in
'2003'!G9, while I would expect nothing (0).

Can you re-check it?

Thank you
Alex

"Arvi Laanemets" wrote:

Hi

=IF('2003'!G9="","",('2005 Comp to 2003'!G9<"")*SUM('2003'!G9,-'2005 Comp
to 2003'!G9))

(there was an error in previous formula, it had to be
'=IF(A2="","",(B2<"")*SUM(A2,-B2))')

What does the formula do?

IF('2003'!G9="","",...) checks the cell G9 on sheet 2003, and when this is
empty, nothing is returned. In case you wanted nothing to be returned with
cell G9 on either sheet 2003 or sheet '2005 ...' , the formula would be:
=IF(OR('2003'!G9="",'2005 Comp to 2003'!G9=""),"",SUM('2003'!G9,-'2005 Comp
to 2003'!G9))

You wanted the formula to return 0, when cell G9 on sheet '2005 ...' is
empty. The ('2005 Comp to 2003'!G9<"") part returns TRUE or FALSE - which
in Excel are interpreted as 1 or 0 , when multiplied with (or added to)
numeric values. So whatever value the SUM returns, when there is no entry in
cell G9, this value is multiplied with 0, and 0 is returned. Otherwise the
sum is multiplied with 1, and the sum is returned.

Now the last part of formula. You used ISERROR to check for empty cells,
because mathematical operators don't work with empty cells or with cells
containing strings. But SUM function simply ignores such cells. So when cell
'2005 Comp to 2003'!G9 is empty (or contains p.e. word "empty"), only cell
'2003'!G9 is summed. No errors anymore.


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Metallo" wrote in message
...
Hi Arvi,

Could you please explain abit the formula?

This is what I have as a real example:

=IF(ISERROR('2003'!G9-'2005 Comp to 2003'!G9),0,'2003'!G9-'2005 Comp to
2003'!G9)

I should I change this it?

Thank you
Alex

"Arvi Laanemets" wrote:

Hi

P.e. with formula on row 2
=IF(B2="","",(B2<"")*SUM(A2,-B2))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Metallo" wrote in message
...
Hi,

I have a WB, with 3 WSs in it.
Let's call them, A,B,C

The numbers in C are given by A - B
=IF(ISERROR('A-'B),0,'A-B)

The problem is that if I have no data in B then I get A numbers into C

How can I add a third condition to the formula so that when no data
are
present in B then it should show Nothing in C?

Thank you!
Alex