Formula Problem - interrupted by #VALUE! in other cells!?

Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)

The cell references may differ, but the formula is the same for all

Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:
Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)


The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:

Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:
Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)


The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Ted

I don't get any error with this formula.
With 1 in H27 and Null in G48 from another formula, it returns 0.
Send me a copy of your sheet directly and I will take a look for you.
Remove NOSPAM from my address to send direct.

Regards

Roger Govier


Ted wrote:
Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:


Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:

Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)



The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Roger, sorry for the delay - yeah, its not the formula that you sent that
causes a problem or anything, its that the one formula (the original/initial
formula that these others get the data from) can generate the #VALUE/0! error
because it is dividing the answer of previous sums. If there is no data in
one of those cells, then it hits a problem because it cant divide 0 by 0 etc;
so gives the #VALUE/0! error message.

Then, when when I ask for the ABS value to be displayed in a final set of
cells, it carries the #VALUE/0! error with it (because its the content of its
dependant cell).

So, what I was hoping to do, is add something to the last or one from last
formula to 'weed-out'/remove the error text/values, and replace them with
nothing.

This means that I need a formula that basically says:

original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN
give answer "" '

{where "" means blank space/empty cell etc}

Any idea how I can do tis please???

Ted.


"Roger Govier" wrote:

Hi Ted

I don't get any error with this formula.
With 1 in H27 and Null in G48 from another formula, it returns 0.
Send me a copy of your sheet directly and I will take a look for you.
Remove NOSPAM from my address to send direct.

Regards

Roger Govier


Ted wrote:
Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:


Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:

Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)



The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Ted

As I said, send me a copy. It is easier than trying to describe where all
the potential pitfalls lie.

Regards

Roger Govier


Ted wrote:
Hi Roger, sorry for the delay - yeah, its not the formula that you sent that
causes a problem or anything, its that the one formula (the original/initial
formula that these others get the data from) can generate the #VALUE/0! error
because it is dividing the answer of previous sums. If there is no data in
one of those cells, then it hits a problem because it cant divide 0 by 0 etc;
so gives the #VALUE/0! error message.

Then, when when I ask for the ABS value to be displayed in a final set of
cells, it carries the #VALUE/0! error with it (because its the content of its
dependant cell).

So, what I was hoping to do, is add something to the last or one from last
formula to 'weed-out'/remove the error text/values, and replace them with
nothing.

This means that I need a formula that basically says:

original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN
give answer "" '

{where "" means blank space/empty cell etc}

Any idea how I can do tis please???

Ted.


"Roger Govier" wrote:


Hi Ted

I don't get any error with this formula.
With 1 in H27 and Null in G48 from another formula, it returns 0.
Send me a copy of your sheet directly and I will take a look for you.
Remove NOSPAM from my address to send direct.

Regards

Roger Govier


Ted wrote:

Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:



Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:


Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)




The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Roger, thanks for getting back to me. Unfortunately I am very restricted
in what I can send to you, as the database is part of a larger project, being
conducted in a social science department of a University; where unfortunate,
and very ridged guidelines apply. To make things more difficult still, the
database contains actual subject data and information – for me to delete this
out, would then limit what can be seen, in terms of what has to be done to
make the database perform as intended etc. Thank you for the offer though, it
is appreciated. I apologise for the unusual situation, and hope there is a
way you can still offer advice.

With the term ‘all I need’ being used very loosely; all I need really
speaking is a formula that tells excel to show the absolute value of a cell
(e.g. content of A1 displayed in A20). I need it to change any values that
are anything but a number to a blank or a zero (blank is preferable); and
need the formula to be encompassed in this formula: =ABS(A1)

Thanks Roger, and sorry again for not being able to make things easier.

Kind regards and speak soon,

Ted.


"Roger Govier" wrote:

Hi Ted

As I said, send me a copy. It is easier than trying to describe where all
the potential pitfalls lie.

Regards

Roger Govier


Ted wrote:
Hi Roger, sorry for the delay - yeah, its not the formula that you sent that
causes a problem or anything, its that the one formula (the original/initial
formula that these others get the data from) can generate the #VALUE/0! error
because it is dividing the answer of previous sums. If there is no data in
one of those cells, then it hits a problem because it cant divide 0 by 0 etc;
so gives the #VALUE/0! error message.

Then, when when I ask for the ABS value to be displayed in a final set of
cells, it carries the #VALUE/0! error with it (because its the content of its
dependant cell).

So, what I was hoping to do, is add something to the last or one from last
formula to 'weed-out'/remove the error text/values, and replace them with
nothing.

This means that I need a formula that basically says:

original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN
give answer "" '

{where "" means blank space/empty cell etc}

Any idea how I can do tis please???

Ted.


"Roger Govier" wrote:


Hi Ted

I don't get any error with this formula.
With 1 in H27 and Null in G48 from another formula, it returns 0.
Send me a copy of your sheet directly and I will take a look for you.
Remove NOSPAM from my address to send direct.

Regards

Roger Govier


Ted wrote:

Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:



Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:


Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)




The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Ted

I fully understand about the confidentiality of data. I am just concerned to
know the contents of various cells.
You posted
=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))
as giving a # VALUE error in any data is missing from the cells.
I replied with a suggestion of
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))
which you say does not sort the problem (it didn't return an error for me
with the test data I made up).

Can you tell me then, what is in
G23, G45, G46, G48 and G47.

Also, you said that
The below formula is then used to show the absolute value of that answer in
another set of cells
=IF(N(CO23)=0,"",(ABS(CO23)))

from this I deduce that the formula above is in cell CO23, is this correct?

and you say
the error code because it mucks/stops up other formula:
=MATCH(MAX(B52:AD52),B52:AD52,0)

so I can't see why CO23 would affect this last formula which is only looking
at row 52.

Can you clarify?


Regards

Roger Govier


Ted wrote:
Hi Roger, thanks for getting back to me. Unfortunately I am very restricted
in what I can send to you, as the database is part of a larger project, being
conducted in a social science department of a University; where unfortunate,
and very ridged guidelines apply. To make things more difficult still, the
database contains actual subject data and information – for me to delete this
out, would then limit what can be seen, in terms of what has to be done to
make the database perform as intended etc. Thank you for the offer though, it
is appreciated. I apologise for the unusual situation, and hope there is a
way you can still offer advice.

With the term ‘all I need’ being used very loosely; all I need really
speaking is a formula that tells excel to show the absolute value of a cell
(e.g. content of A1 displayed in A20). I need it to change any values that
are anything but a number to a blank or a zero (blank is preferable); and
need the formula to be encompassed in this formula: =ABS(A1)

Thanks Roger, and sorry again for not being able to make things easier.

Kind regards and speak soon,

Ted.


"Roger Govier" wrote:


Hi Ted

As I said, send me a copy. It is easier than trying to describe where all
the potential pitfalls lie.

Regards

Roger Govier


Ted wrote:

Hi Roger, sorry for the delay - yeah, its not the formula that you sent that
causes a problem or anything, its that the one formula (the original/initial
formula that these others get the data from) can generate the #VALUE/0! error
because it is dividing the answer of previous sums. If there is no data in
one of those cells, then it hits a problem because it cant divide 0 by 0 etc;
so gives the #VALUE/0! error message.

Then, when when I ask for the ABS value to be displayed in a final set of
cells, it carries the #VALUE/0! error with it (because its the content of its
dependant cell).

So, what I was hoping to do, is add something to the last or one from last
formula to 'weed-out'/remove the error text/values, and replace them with
nothing.

This means that I need a formula that basically says:

original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN
give answer "" '

{where "" means blank space/empty cell etc}

Any idea how I can do tis please???

Ted.


"Roger Govier" wrote:



Hi Ted

I don't get any error with this formula.
With 1 in H27 and Null in G48 from another formula, it returns 0.
Send me a copy of your sheet directly and I will take a look for you.
Remove NOSPAM from my address to send direct.

Regards

Roger Govier


Ted wrote:


Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:




Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:



Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)





The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Roger,

G23 = data returned by other formula, the rest of the cells are means,
standard deviations and ranges. The reason why the #VALUE/0! is returned, I
assume at least, is because at times there is only one entry in the dataset -
part of the formula
=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))
is looking at means and things like that - things that are dependant on
there being more than one entry in the dataset (hence not being able to
divide by 0).

the best place for something to be added to remove the #VALUE/0! error code
is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula
was where I had tried to get around the above problem) or in the =(ABS(Q23))
formula. the final formula is affected because it is looking for the highest
score, which it would appear is the #VALUE/0! error code!

so to reduce the possibility of there being an effect on other formula, the
favored place for a preventative formula to be added is in the =(ABS(xxx...)
formula scenes its only function is to show the content of another cell/ set
of cells. If there is a way, I just want to get excel to detect anything
other than numbers, and transfer it to a blank or to a zero???

Thanks for your help $;-D

I will be offline for the next 2hrs, but will be back online for a few hours
then - speak soon and thanks again.

Ted.

"Roger Govier" wrote:

Hi Ted

I fully understand about the confidentiality of data. I am just concerned to
know the contents of various cells.
You posted
=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))
as giving a # VALUE error in any data is missing from the cells.
I replied with a suggestion of
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))
which you say does not sort the problem (it didn't return an error for me
with the test data I made up).

Can you tell me then, what is in
G23, G45, G46, G48 and G47.

Also, you said that
The below formula is then used to show the absolute value of that answer in
another set of cells
=IF(N(CO23)=0,"",(ABS(CO23)))

from this I deduce that the formula above is in cell CO23, is this correct?

and you say
the error code because it mucks/stops up other formula:
=MATCH(MAX(B52:AD52),B52:AD52,0)

so I can't see why CO23 would affect this last formula which is only looking
at row 52.

Can you clarify?


Regards

Roger Govier


Ted wrote:
Hi Roger, thanks for getting back to me. Unfortunately I am very restricted
in what I can send to you, as the database is part of a larger project, being
conducted in a social science department of a University; where unfortunate,
and very ridged guidelines apply. To make things more difficult still, the
database contains actual subject data and information – for me to delete this
out, would then limit what can be seen, in terms of what has to be done to
make the database perform as intended etc. Thank you for the offer though, it
is appreciated. I apologise for the unusual situation, and hope there is a
way you can still offer advice.

With the term ‘all I need’ being used very loosely; all I need really
speaking is a formula that tells excel to show the absolute value of a cell
(e.g. content of A1 displayed in A20). I need it to change any values that
are anything but a number to a blank or a zero (blank is preferable); and
need the formula to be encompassed in this formula: =ABS(A1)

Thanks Roger, and sorry again for not being able to make things easier.

Kind regards and speak soon,

Ted.


"Roger Govier" wrote:


Hi Ted

As I said, send me a copy. It is easier than trying to describe where all
the potential pitfalls lie.

Regards

Roger Govier


Ted wrote:

Hi Roger, sorry for the delay - yeah, its not the formula that you sent that
causes a problem or anything, its that the one formula (the original/initial
formula that these others get the data from) can generate the #VALUE/0! error
because it is dividing the answer of previous sums. If there is no data in
one of those cells, then it hits a problem because it cant divide 0 by 0 etc;
so gives the #VALUE/0! error message.

Then, when when I ask for the ABS value to be displayed in a final set of
cells, it carries the #VALUE/0! error with it (because its the content of its
dependant cell).

So, what I was hoping to do, is add something to the last or one from last
formula to 'weed-out'/remove the error text/values, and replace them with
nothing.

This means that I need a formula that basically says:

original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN
give answer "" '

{where "" means blank space/empty cell etc}

Any idea how I can do tis please???

Ted.


"Roger Govier" wrote:



Hi Ted

I don't get any error with this formula.
With 1 in H27 and Null in G48 from another formula, it returns 0.
Send me a copy of your sheet directly and I will take a look for you.
Remove NOSPAM from my address to send direct.

Regards

Roger Govier


Ted wrote:


Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:




Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:



Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)





The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.

Hi Ted

so to reduce the possibility of there being an effect on other formula, the
favored place for a preventative formula to be added is in the
=(ABS(xxx...)

I can't agree with you there.
I think you need to ensure that any errors are trapped at their earliest
possible occurrence, not way down the line through other formulae.

You need to look at the formulae that give rise to the values in G23 etc.
and trap there for the existence of sufficient values for the formula to be
evaluated without giving rise to the #DIV/0 error.

Post examples of the formulae that give rise to the values in the cells that
I requested in my previous posting, then I (and/or others) can help you fix
that problem.


Regards

Roger Govier


Ted wrote:
Hi Roger,

G23 = data returned by other formula, the rest of the cells are means,
standard deviations and ranges. The reason why the #VALUE/0! is returned, I
assume at least, is because at times there is only one entry in the dataset -
part of the formula
=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))
is looking at means and things like that - things that are dependant on
there being more than one entry in the dataset (hence not being able to
divide by 0).

the best place for something to be added to remove the #VALUE/0! error code
is in either =ROUND(H23*G$48,3) (the additional bit you say on this formula
was where I had tried to get around the above problem) or in the =(ABS(Q23))
formula. the final formula is affected because it is looking for the highest
score, which it would appear is the #VALUE/0! error code!

so to reduce the possibility of there being an effect on other formula, the
favored place for a preventative formula to be added is in the =(ABS(xxx...)
formula scenes its only function is to show the content of another cell/ set
of cells. If there is a way, I just want to get excel to detect anything
other than numbers, and transfer it to a blank or to a zero???

Thanks for your help $;-D

I will be offline for the next 2hrs, but will be back online for a few hours
then - speak soon and thanks again.

Ted.

"Roger Govier" wrote:


Hi Ted

I fully understand about the confidentiality of data. I am just concerned to
know the contents of various cells.
You posted
=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))
as giving a # VALUE error in any data is missing from the cells.
I replied with a suggestion of
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47))
which you say does not sort the problem (it didn't return an error for me
with the test data I made up).

Can you tell me then, what is in
G23, G45, G46, G48 and G47.

Also, you said that
The below formula is then used to show the absolute value of that answer in
another set of cells
=IF(N(CO23)=0,"",(ABS(CO23)))

from this I deduce that the formula above is in cell CO23, is this correct?

and you say
the error code because it mucks/stops up other formula:
=MATCH(MAX(B52:AD52),B52:AD52,0)

so I can't see why CO23 would affect this last formula which is only looking
at row 52.

Can you clarify?


Regards

Roger Govier


Ted wrote:

Hi Roger, thanks for getting back to me. Unfortunately I am very restricted
in what I can send to you, as the database is part of a larger project, being
conducted in a social science department of a University; where unfortunate,
and very ridged guidelines apply. To make things more difficult still, the
database contains actual subject data and information – for me to delete this
out, would then limit what can be seen, in terms of what has to be done to
make the database perform as intended etc. Thank you for the offer though, it
is appreciated. I apologise for the unusual situation, and hope there is a
way you can still offer advice.

With the term ‘all I need’ being used very loosely; all I need really
speaking is a formula that tells excel to show the absolute value of a cell
(e.g. content of A1 displayed in A20). I need it to change any values that
are anything but a number to a blank or a zero (blank is preferable); and
need the formula to be encompassed in this formula: =ABS(A1)

Thanks Roger, and sorry again for not being able to make things easier.

Kind regards and speak soon,

Ted.


"Roger Govier" wrote:



Hi Ted

As I said, send me a copy. It is easier than trying to describe where all
the potential pitfalls lie.

Regards

Roger Govier


Ted wrote:


Hi Roger, sorry for the delay - yeah, its not the formula that you sent that
causes a problem or anything, its that the one formula (the original/initial
formula that these others get the data from) can generate the #VALUE/0! error
because it is dividing the answer of previous sums. If there is no data in
one of those cells, then it hits a problem because it cant divide 0 by 0 etc;
so gives the #VALUE/0! error message.

Then, when when I ask for the ABS value to be displayed in a final set of
cells, it carries the #VALUE/0! error with it (because its the content of its
dependant cell).

So, what I was hoping to do, is add something to the last or one from last
formula to 'weed-out'/remove the error text/values, and replace them with
nothing.

This means that I need a formula that basically says:

original formula " =ABS(DM23) " Plus 'IF answer = "#VALUE/0!" THEN
give answer "" '

{where "" means blank space/empty cell etc}

Any idea how I can do tis please???

Ted.


"Roger Govier" wrote:




Hi Ted

I don't get any error with this formula.
With 1 in H27 and Null in G48 from another formula, it returns 0.
Send me a copy of your sheet directly and I will take a look for you.
Remove NOSPAM from my address to send direct.

Regards

Roger Govier


Ted wrote:



Hi, no sorry, that doesnt work - I think it needs to be inc in the
=IF(N(H27)=0,"",ROUND(H27*G$48,3)) formula as well, so that it is not
affected by other formula, other than the absoult value one - any
suggestions??

Thanks again, Ted.


"Roger Govier" wrote:





Hi Ted

Perhaps
=IF(OR(G23G$45,G23G$46,G$471),"",IF(G$4 80,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:




Hi,

The below formula

=IF(OR(G23G$45,G23G$46),"",IF(G$480,(G 23-G$45)/G$47,(G$46-G23)/G$47))

is used to generate data. If there is only one data in the set, then it
returns a #VALUE! Error message (as expected)

The below formula is then used to show the absolute value of that answer in
another set of cells

=IF(N(CO23)=0,"",(ABS(CO23)))

The problem being, I do need the absolute value of the data from those
cells, but not the error code because it mucks/stops up other formula:

=MATCH(MAX(B52:AD52),B52:AD52,0)






The cell references may differ, but the formula is the same for all


Does anyone know of a way to get the second formula to change #VALUE! for an
empty cell, as the answer, when the #VALUE! error is generated, but transfer
all numbers (where there are any) in tact!??

Thanks in advance,
Ted.