Thread: How can improve this formula?

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<at>tarkon.ee
Arvi Laanemets


"Metallo" <Metallo@discussions.microsoft.com> wrote in message
news:5B7B79E8-59AF-481D-9AC6-C16AB057B08F@microsoft.com...
> 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<at>tarkon.ee
> Arvi Laanemets
>
>
> "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> news:5B7B79E8-59AF-481D-9AC6-C16AB057B08F@microsoft.com...
> > 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<at>tarkon.ee
Arvi Laanemets



"Metallo" <Metallo@discussions.microsoft.com> wrote in message
news:CDD9FE56-8D03-40B5-9ABB-D873BA3C8E9E@microsoft.com...
> 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<at>tarkon.ee
> > Arvi Laanemets
> >
> >
> > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > news:5B7B79E8-59AF-481D-9AC6-C16AB057B08F@microsoft.com...
> > > 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<at>tarkon.ee
> Arvi Laanemets
>
>
>
> "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> news:CDD9FE56-8D03-40B5-9ABB-D873BA3C8E9E@microsoft.com...
> > 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<at>tarkon.ee
> > > Arvi Laanemets
> > >
> > >
> > > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > > news:5B7B79E8-59AF-481D-9AC6-C16AB057B08F@microsoft.com...
> > > > 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<at>tarkon.ee
> > Arvi Laanemets
> >
> >
> >
> > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > news:CDD9FE56-8D03-40B5-9ABB-D873BA3C8E9E@microsoft.com...
> > > 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<at>tarkon.ee
> > > > Arvi Laanemets
> > > >
> > > >
> > > > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > > > news:5B7B79E8-59AF-481D-9AC6-C16AB057B08F@microsoft.com...
> > > > > 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,

It works if I substitute "" with 0

Thanks
ALex

"Metallo" wrote:

> 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<at>tarkon.ee
> > > Arvi Laanemets
> > >
> > >
> > >
> > > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > > news:CDD9FE56-8D03-40B5-9ABB-D873BA3C8E9E@microsoft.com...
> > > > 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<at>tarkon.ee
> > > > > Arvi Laanemets
> > > > >
> > > > >
> > > > > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > > > > news:5B7B79E8-59AF-481D-9AC6-C16AB057B08F@microsoft.com...
> > > > > > 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

I tried it again with single sheet version
=IF(A2="","",(B2<>"")*SUM(A2,-B2))
returns nothing, when in A2 is nothing, or when there is a formula which
returns "". But it returns -B2, when there is non-empty string, p.e. " ", or
when some formula returns such string. You can check for it in your formula,
modifying it as follows:
=IF(TRIM('2003'!G9)="","",('2005 Comp to 2003'!G9<>"")*SUM('2003'!G9,-'2005
Comp to 2003'!G9))
When this returns nothing with '2003'!G9 without a visible value, then there
is a space in cell/returned by formula.

The same with another check - when there is (returned, when there is a
formula) anything different from "", the check returns true.
When it works after "" is replaced by 0 (in which check?), then maybe your
formula returns 0, and you have set not to show zeros (in Tools.Options.View
the 'Zero values' control is unchecked).


Arvi Laanemets


"Metallo" <Metallo@discussions.microsoft.com> wrote in message
news:C909C709-90AE-4684-A664-DD44833EA041@microsoft.com...
> Arvi,
>
> It works if I substitute "" with 0
>
> Thanks
> ALex
>
> "Metallo" wrote:
>
> > 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<at>tarkon.ee
> > > > Arvi Laanemets
> > > >
> > > >
> > > >
> > > > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > > > news:CDD9FE56-8D03-40B5-9ABB-D873BA3C8E9E@microsoft.com...
> > > > > 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<at>tarkon.ee
> > > > > > Arvi Laanemets
> > > > > >
> > > > > >
> > > > > > "Metallo" <Metallo@discussions.microsoft.com> wrote in message
> > > > > > news:5B7B79E8-59AF-481D-9AC6-C16AB057B08F@microsoft.com...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >