How to correct this formula and check if more issues are existing?
 

Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that you can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by chance, I
really don't know if this happens somewhere else in the other WSs and where.
Therefore, I need something that I can apply to all my WBs and automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are existing?
 

=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet
3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are existing?
 

Worksheet formula:
=IF(ISERROR(Sheet2!I47/Sheet2!I47),Sheet3!A1,I45*Sheet2!I47/1000) or
=IF(Sheet2!I47=0,Sheet3!A1,I45*Sheet2!I47/1000)

The ISERROR was written incorrectly because 0/1000 = 0, not ERROR, so it
would never evaluate to TRUE. You were getting the 0 or empty cell (you must
have display 0's option turned off) from the False statement. In this case
you don't need the ISERROR anyway.

Mike F
"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are existing?
 

Nevermind. Tom is correct, I misread your intent as just wanting the value
from sheet3.
"Mike Fogleman" wrote in message
news:kBqfd.9754$R05.2534@attbi_s53...
Worksheet formula:
=IF(ISERROR(Sheet2!I47/Sheet2!I47),Sheet3!A1,I45*Sheet2!I47/1000) or
=IF(Sheet2!I47=0,Sheet3!A1,I45*Sheet2!I47/1000)

The ISERROR was written incorrectly because 0/1000 = 0, not ERROR, so it
would never evaluate to TRUE. You were getting the 0 or empty cell (you
must
have display 0's option turned off) from the False statement. In this case
you don't need the ISERROR anyway.

Mike F
"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by
chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are exist
 

Tom,

I think there's a problem in your formula, probably I did not explain well
enough.
Let's try again.

In a cell of Sheet1 (which is the cell which has to give the result) there's
this formula:
=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)
If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

If there is no value then the cell in Sheet1 should pick up a value from
Sheet3, this value is a plain value to be picked up.
From your formula, it seems to me that you multiply I45*Sheet3, but in
reality I just need my cell to pick up the value as it is in that cell of
Sheet3.

Hope it's clearer

Thank you
Alex

"Tom Ogilvy" wrote:

=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet
3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are exist
 

Tom,

As previously said, your formula does not work.
I wonder why this formula cannot do the job either:

=IF(ISERROR(I45*'Sheet2'!I47/1000),'Sheet3!I46,I45*'Sheet2'!I47/1000)

If Sheet2 gives error (because I45*empty cellI47/1000), then pick up
'Sheet3!I46 otherwise do the calcultaion 'Sheet2'!I47/1000

It seems logic to me, but it doesn't give me any result.

Has this something to do with the fact that the cell I47/1000 does not
contain any value?

Thanks
Alex

"Tom Ogilvy" wrote:

=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet
3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are exist
 

If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

that is not the case with the formula you show. With the formula you show,
it returns 0 if the cell (Sheet2!I47) is empty.

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Tom,

I think there's a problem in your formula, probably I did not explain well
enough.
Let's try again.

In a cell of Sheet1 (which is the cell which has to give the result)
there's
this formula:
=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)
If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

If there is no value then the cell in Sheet1 should pick up a value from
Sheet3, this value is a plain value to be picked up.
From your formula, it seems to me that you multiply I45*Sheet3, but in
reality I just need my cell to pick up the value as it is in that cell of
Sheet3.

Hope it's clearer

Thank you
Alex

"Tom Ogilvy" wrote:


=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet
3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that
you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by
chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are exist
 

Tom,

I don't understand.
Why does it return 0 if I say return 'Sheet3!I46?

=IF(ISERROR(I45*'Sheet2'!I47/1000),'Sheet3!I46,I45*'Sheet2'!I47/1000)

IF I45*'Sheet2'!I47/1000 ISERROR, then pick up the value in 'Sheet3!I46,
otherwise return the result ot the operation, that is I45*'Sheet2'!I47/1000.

Where am I wrong? :(

Alex



"Tom Ogilvy" wrote:

If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

that is not the case with the formula you show. With the formula you show,
it returns 0 if the cell (Sheet2!I47) is empty.

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Tom,

I think there's a problem in your formula, probably I did not explain well
enough.
Let's try again.

In a cell of Sheet1 (which is the cell which has to give the result)
there's
this formula:
=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)
If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

If there is no value then the cell in Sheet1 should pick up a value from
Sheet3, this value is a plain value to be picked up.
From your formula, it seems to me that you multiply I45*Sheet3, but in
reality I just need my cell to pick up the value as it is in that cell of
Sheet3.

Hope it's clearer

Thank you
Alex

"Tom Ogilvy" wrote:


=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet
3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that
you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by
chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are exist
 

Tom,

Apparently, the following is working fine for my scope:

=IF('Sheet2!I47 = 0,'Sheet3!I46,I45*'Sheet2'!I47/1000)

Thanks for your "push to understand", however I'd like to know more about
the role of, LEN & TRIM functions, in the formula you gave me initially?

Regards
Alex

"Tom Ogilvy" wrote:

If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

that is not the case with the formula you show. With the formula you show,
it returns 0 if the cell (Sheet2!I47) is empty.

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Tom,

I think there's a problem in your formula, probably I did not explain well
enough.
Let's try again.

In a cell of Sheet1 (which is the cell which has to give the result)
there's
this formula:
=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)
If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

If there is no value then the cell in Sheet1 should pick up a value from
Sheet3, this value is a plain value to be picked up.
From your formula, it seems to me that you multiply I45*Sheet3, but in
reality I just need my cell to pick up the value as it is in that cell of
Sheet3.

Hope it's clearer

Thank you
Alex

"Tom Ogilvy" wrote:


=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet
3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1) is an
empty
cell.
Mathematically is correct, but what I need instead is the following:
If there's no value, then I want the cell in Sheet1 to pick up another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value that
you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by
chance,
I
really don't know if this happens somewhere else in the other WSs and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good solution.

Thank you!
Alex

How to correct this formula and check if more issues are exist
 

This is what you said:

In a cell of Sheet1 (which is the cell which has to give the result) there's
this formula:
=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)
If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

That's wrong.

This statement is wrong as well:
.. . . Sheet2 gives error (because I45*empty cellI47/1000),

I45 times an empty cell (event if then divided by 1000) gives zero, not an
error.

You need to do a little testing to see what you results are.

So I put a lot of credence in your advice that my formula doesn't work -
although I don't doubt it doesn't do what you want since your need statement
was not that clear.

If you just want to return the sheet3 value if the sheet2 cell (I47) is
blank

=if(len(trim('Sheet2'!I47))=0,'Sheet3'!I47,I45*'Sh eet2'!I47/1000)

--
Regards,
Tom Ogilvy



"Metallo" wrote in message
...
Tom,

I don't understand.
Why does it return 0 if I say return 'Sheet3!I46?

=IF(ISERROR(I45*'Sheet2'!I47/1000),'Sheet3!I46,I45*'Sheet2'!I47/1000)

IF I45*'Sheet2'!I47/1000 ISERROR, then pick up the value in 'Sheet3!I46,
otherwise return the result ot the operation, that is
I45*'Sheet2'!I47/1000.

Where am I wrong? :(

Alex



"Tom Ogilvy" wrote:

If Sheet2 is empty, the cell in Sheet1 will give no value (empty
cell)

that is not the case with the formula you show. With the formula you
show,
it returns 0 if the cell (Sheet2!I47) is empty.

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Tom,

I think there's a problem in your formula, probably I did not explain
well
enough.
Let's try again.

In a cell of Sheet1 (which is the cell which has to give the result)
there's
this formula:
=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)
If Sheet2 is empty, the cell in Sheet1 will give no value (empty cell)

If there is no value then the cell in Sheet1 should pick up a value
from
Sheet3, this value is a plain value to be picked up.
From your formula, it seems to me that you multiply I45*Sheet3, but in
reality I just need my cell to pick up the value as it is in that cell
of
Sheet3.

Hope it's clearer

Thank you
Alex

"Tom Ogilvy" wrote:



=if(len(trim('Sheet2'!I47))=0,IF(ISERROR(I45*'Shee t3'!I47/1000),0,I45*'Sheet

3'!I47/1000),IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000))

--
Regards,
Tom Ogilvy


"Metallo" wrote in message
...
Hi,

I have 11 WBs structured exactly the same way.
Every WB contains 30 WSs, the WSs Tab names are the same for every
WB.

Now the problem:

One WS (Sheet1) gives results performing a calculation that
involves
another
WS (Sheet2).
Below is a typical formula in a cell

=IF(ISERROR(I45*'Sheet2'!I47/1000),0,I45*'Sheet2'!I47/1000)

Everything works fine if in 'Sheet2'!I47 there's a value.
But if there's no value, obviously what I get in the WS (Sheet1)
is an
empty
cell.
Mathematically is correct, but what I need instead is the
following:
If there's no value, then I want the cell in Sheet1 to pick up
another
value
in another WS (Sheet3).
Basically the instruction should be:
If you don't find any value in 'Sheet2'!I47 then put the value
that
you
can
find in Sheet3!A1

Probably I can do this in two ways:

1) Changing the worksheet formula, but I don't know how.
2) Programmatically (also no idea how)

Thats where I need your advise, since I discovered this problem by
chance,
I
really don't know if this happens somewhere else in the other WSs
and
where.
Therefore, I need something that I can apply to all my WBs and
automatically
apply the new formula were it is needed.

I hope this is clear enough and allows you to give me a good
solution.

Thank you!
Alex