Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi,

The below formula

=IF(OR(G23<G$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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Ted

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

Regards

Roger Govier


Ted wrote:
Hi,

The below formula

=IF(OR(G23<G$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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$45,G23G$46,G$47<1),"",IF(G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:
Hi,

The below formula

=IF(OR(G23<G$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.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$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.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$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.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$45,G23G$46,G$47<1),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:


Hi,

The below formula

=IF(OR(G23<G$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.


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$45,G23G$46,G$47<1),"",IF(G$480, (G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:


Hi,

The below formula

=IF(OR(G23<G$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.



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$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.


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$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.



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

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(G23<G$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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$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.




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Roger, as I say, the formula
=IF(OR(G24<G$45,G24G$46),"",IF(G$480,(G24-G$45)/G$47,(G$46-G24)/G$47))
causes the error as it is dependant on cells which look at standard
deviation, means and stuff like that (in G45:48) - if there is only one data
entry, then these values cannot be worked out, so it generates the #VALUE/0!
error. That part is ok and is acceptable. But the final ABS formula takes the
error codes to a set of cells, where the largest is highlighted using
=MATCH(MAX(B50:AD50),B50:AD50,0). If ABS brings an error code to the B50:AD50
cells, then the MAX formula classes them as the greatest value.

Because the formulas prior to the =(ABS(... formula are all dependant on
means and standard deviations, there is no way to prevent the error from
being generated when only one data is available. as far as analysis goes, it
does not matter either as any missing data does not matter because the number
of subjects evens that sort of stuff out. thats why I think that the best
place might be the ABS formula - what do you think?? Also, prior formula is
also dependant on correlations within the G40-odd cells. again, meaning that
error codes are generated, as expected (that part is fully acceptable though,
fortunatelly).

Let me know what oyu think please, and thanks again for oyur help.

P.S. sorry for the extended 2hrs.

Ted.


"Roger Govier" wrote:

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(G23<G$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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$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.



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Roger, just to let you know, I have worked it out by using:

=IF(ISERROR(ABS(A50)),"",ABS(A50))

thank you for your help though $;-)

thanks again,
Ted.

"Roger Govier" wrote:

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(G23<G$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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$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.



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Ted

I'm glad you have got it worked out to your satisfaction.
However, I still believe that intrinsically, all errors should be trapped at
root. Allowing them to pervade through a series of further calculations
before finally trapping them, COULD result in erroneous conclusions.

You have still not said what these earlier formulae are, other than "means,
SD's and the like",and I am sure it would be possible to trap errors at this
level, which MAY have a bearing upon later outcomes.

Still, its your call.

If you are based in the States Ted, enjoy the remainder of Thanksgiving.

Regards

Roger Govier


Ted wrote:
Hi Roger, just to let you know, I have worked it out by using:

=IF(ISERROR(ABS(A50)),"",ABS(A50))

thank you for your help though $;-)

thanks again,
Ted.

"Roger Govier" wrote:


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(G23<G$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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",IF(G $480,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:





Hi,

The below formula

=IF(OR(G23<G$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.


  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Roger, I'm construction a reply now, giving further details - speak in a
few min...

Ted.

P.S. Happy Thanksgiving too

"Roger Govier" wrote:

Hi Ted

I'm glad you have got it worked out to your satisfaction.
However, I still believe that intrinsically, all errors should be trapped at
root. Allowing them to pervade through a series of further calculations
before finally trapping them, COULD result in erroneous conclusions.

You have still not said what these earlier formulae are, other than "means,
SD's and the like",and I am sure it would be possible to trap errors at this
level, which MAY have a bearing upon later outcomes.

Still, its your call.

If you are based in the States Ted, enjoy the remainder of Thanksgiving.

Regards

Roger Govier


Ted wrote:
Hi Roger, just to let you know, I have worked it out by using:

=IF(ISERROR(ABS(A50)),"",ABS(A50))

thank you for your help though $;-)

thanks again,
Ted.

"Roger Govier" wrote:


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(G23<G$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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",IF(G $480,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:





Hi,

The below formula

=IF(OR(G23<G$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.



  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Roger, here is a list of what is in each cell respectively:

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

G23 - data
G45 €“ mean
G46 €“ standard deviation
G47 €“ 1st range
G48 €“ 2nd range

So as you can seen because of the €¦,(G23-G$45)/G$47,.. part, when there is
no data in G23, and excel goes to perform the division between mean/1st
range, it cant do it and generates a #VALUE/0! Error.

The data or/and error codes are then picked up by this formula in cell
H23:25 etc

=IF(N(H23)=0,"",ROUND(H23*G$48,3))

the above gets rid of any zeros and replaces them with empty cells/blank
spaces in cell I23.

The data generated by that formula is then transferred to a different part
of the spreadsheet, using the new formula of

=IF(ISERROR(ABS(I23)),"",ABS(I23))

{the one I was having trouble with}

All data is then arranged in a line, so the greatest value can be
highlighted, using this formula

=MATCH(MAX(A50:Z50),A50:Z50,0)

This last formula is where the #VALUE/0! became a problem, because it
classes text as the greatest value (or more specifically, #DIV/0! and
#VALUE/0!). Meaning that by removing anything that isnt a number becomes a
necessity, otherwise you are forced to manually remove them from the arranged
results. The IF(ISERROR is now stopping the text/error codes from progressing
as far as the final tables, so to speak; resulting in =, what at least
appears to be, success!

What say you??

Ted.


"Roger Govier" wrote:

Hi Ted

I'm glad you have got it worked out to your satisfaction.
However, I still believe that intrinsically, all errors should be trapped at
root. Allowing them to pervade through a series of further calculations
before finally trapping them, COULD result in erroneous conclusions.

You have still not said what these earlier formulae are, other than "means,
SD's and the like",and I am sure it would be possible to trap errors at this
level, which MAY have a bearing upon later outcomes.

Still, its your call.

If you are based in the States Ted, enjoy the remainder of Thanksgiving.

Regards

Roger Govier


Ted wrote:
Hi Roger, just to let you know, I have worked it out by using:

=IF(ISERROR(ABS(A50)),"",ABS(A50))

thank you for your help though $;-)

thanks again,
Ted.

"Roger Govier" wrote:


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(G23<G$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(G23<G$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(G23<G$45,G23G$46,G$47<1),"",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(G23<G$45,G23G$46,G$47<1),"",IF(G $480,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:





Hi,

The below formula

=IF(OR(G23<G$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.





  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Ted

Then I think a simple extra IF on that first calculation will resolve the
problem.
=IF(G23="","",IF(OR(G23<G$45,G23G$46),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47)))

If this does solve the issue, I can only apologise that we have taken so
long to get here.

Regards

Roger Govier


Ted wrote:
Hi Roger, here is a list of what is in each cell respectively:

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

G23 - data
G45 €“ mean
G46 €“ standard deviation
G47 €“ 1st range
G48 €“ 2nd range

So as you can seen because of the €¦,(G23-G$45)/G$47,.. part, when there is
no data in G23, and excel goes to perform the division between mean/1st
range, it cant do it and generates a #VALUE/0! Error.

The data or/and error codes are then picked up by this formula in cell
H23:25 etc

=IF(N(H23)=0,"",ROUND(H23*G$48,3))

the above gets rid of any zeros and replaces them with empty cells/blank
spaces in cell I23.

The data generated by that formula is then transferred to a different part
of the spreadsheet, using the new formula of

=IF(ISERROR(ABS(I23)),"",ABS(I23))

{the one I was having trouble with}

All data is then arranged in a line, so the greatest value can be
highlighted, using this formula

=MATCH(MAX(A50:Z50),A50:Z50,0)

This last formula is where the #VALUE/0! became a problem, because it
classes text as the greatest value (or more specifically, #DIV/0! and
#VALUE/0!). Meaning that by removing anything that isnt a number becomes a
necessity, otherwise you are forced to manually remove them from the arranged
results. The IF(ISERROR is now stopping the text/error codes from progressing
as far as the final tables, so to speak; resulting in =, what at least
appears to be, success!

What say you??

Ted.


"Roger Govier" wrote:


Hi Ted

I'm glad you have got it worked out to your satisfaction.
However, I still believe that intrinsically, all errors should be trapped at
root. Allowing them to pervade through a series of further calculations
before finally trapping them, COULD result in erroneous conclusions.

You have still not said what these earlier formulae are, other than "means,
SD's and the like",and I am sure it would be possible to trap errors at this
level, which MAY have a bearing upon later outcomes.

Still, its your call.

If you are based in the States Ted, enjoy the remainder of Thanksgiving.

Regards

Roger Govier


Ted wrote:

Hi Roger, just to let you know, I have worked it out by using:

=IF(ISERROR(ABS(A50)),"",ABS(A50))

thank you for your help though $;-)

thanks again,
Ted.

"Roger Govier" wrote:



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(G23<G$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(G23<G$45,G23G$46),"",IF(G$480,(G2 3-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(G23<G$45,G23G$46,G$47<1),"",IF(G$48 0,(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(G23<G$45,G23G$46,G$47<1),"",IF (G$480,(G23-G$45)/G$47,(G$46-G23)/G$47))

Regards

Roger Govier


Ted wrote:






Hi,

The below formula

=IF(OR(G23<G$45,G23G$46),"",IF(G$48 0,(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.


  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ted
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Roger, thanks for the help €“ It does get rid of some of the errors, but
because they are generated by there only being one entry in a dataset, at
times, then an error code still occurs (but only in the cell that has the
data €“ saying that there is nothing to divide it by {thats expected though}.

Thanks for your help and time.

Kind regards,
Ted

"Roger Govier" wrote:

Hi Ted

Then I think a simple extra IF on that first calculation will resolve the
problem.
=IF(G23="","",IF(OR(G23<G$45,G23G$46),"",IF(G$48 0,(G23-G$45)/G$47,(G$46-G23)/G$47)))

If this does solve the issue, I can only apologise that we have taken so
long to get here.

Regards

Roger Govier


Ted wrote:
Hi Roger, here is a list of what is in each cell respectively:

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

G23 - data
G45 €“ mean
G46 €“ standard deviation
G47 €“ 1st range
G48 €“ 2nd range

So as you can seen because of the €¦,(G23-G$45)/G$47,.. part, when there is
no data in G23, and excel goes to perform the division between mean/1st
range, it cant do it and generates a #VALUE/0! Error.

The data or/and error codes are then picked up by this formula in cell
H23:25 etc

=IF(N(H23)=0,"",ROUND(H23*G$48,3))

the above gets rid of any zeros and replaces them with empty cells/blank
spaces in cell I23.

The data generated by that formula is then transferred to a different part
of the spreadsheet, using the new formula of

=IF(ISERROR(ABS(I23)),"",ABS(I23))

{the one I was having trouble with}

All data is then arranged in a line, so the greatest value can be
highlighted, using this formula

=MATCH(MAX(A50:Z50),A50:Z50,0)

This last formula is where the #VALUE/0! became a problem, because it
classes text as the greatest value (or more specifically, #DIV/0! and
#VALUE/0!). Meaning that by removing anything that isnt a number becomes a
necessity, otherwise you are forced to manually remove them from the arranged
results. The IF(ISERROR is now stopping the text/error codes from progressing
as far as the final tables, so to speak; resulting in =, what at least
appears to be, success!

What say you??

Ted.


"Roger Govier" wrote:


Hi Ted

I'm glad you have got it worked out to your satisfaction.
However, I still believe that intrinsically, all errors should be trapped at
root. Allowing them to pervade through a series of further calculations
before finally trapping them, COULD result in erroneous conclusions.

You have still not said what these earlier formulae are, other than "means,
SD's and the like",and I am sure it would be possible to trap errors at this
level, which MAY have a bearing upon later outcomes.

Still, its your call.

If you are based in the States Ted, enjoy the remainder of Thanksgiving.

Regards

Roger Govier


Ted wrote:

Hi Roger, just to let you know, I have worked it out by using:

=IF(ISERROR(ABS(A50)),"",ABS(A50))

thank you for your help though $;-)

thanks again,
Ted.

"Roger Govier" wrote:



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(G23<G$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(G23<G$45,G23G$46),"",IF(G$480,(G2 3-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(G23<G$45,G23G$46,G$47<1),"",IF(G$48 0,(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

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Formula Problem - interrupted by #VALUE! in other cells!?

Hi Ted

Then test the range of the dataset with a COUNT or COUNTIF function and if
the result is less than 1, don't carry out the calculation.

=IF(COUNT(A1:A10)<1,"",your_calculation)

Regards

Roger Govier


Ted wrote:
Hi Roger, thanks for the help €“ It does get rid of some of the errors, but
because they are generated by there only being one entry in a dataset, at
times, then an error code still occurs (but only in the cell that has the
data €“ saying that there is nothing to divide it by {thats expected though}.

Thanks for your help and time.

Kind regards,
Ted

"Roger Govier" wrote:


Hi Ted

Then I think a simple extra IF on that first calculation will resolve the
problem.
=IF(G23="","",IF(OR(G23<G$45,G23G$46),"",IF(G$4 80,(G23-G$45)/G$47,(G$46-G23)/G$47)))

If this does solve the issue, I can only apologise that we have taken so
long to get here.

Regards

Roger Govier


Ted wrote:

Hi Roger, here is a list of what is in each cell respectively:

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

G23 - data
G45 €“ mean
G46 €“ standard deviation
G47 €“ 1st range
G48 €“ 2nd range

So as you can seen because of the €¦,(G23-G$45)/G$47,.. part, when there is
no data in G23, and excel goes to perform the division between mean/1st
range, it cant do it and generates a #VALUE/0! Error.

The data or/and error codes are then picked up by this formula in cell
H23:25 etc

=IF(N(H23)=0,"",ROUND(H23*G$48,3))

the above gets rid of any zeros and replaces them with empty cells/blank
spaces in cell I23.

The data generated by that formula is then transferred to a different part
of the spreadsheet, using the new formula of

=IF(ISERROR(ABS(I23)),"",ABS(I23))

{the one I was having trouble with}

All data is then arranged in a line, so the greatest value can be
highlighted, using this formula

=MATCH(MAX(A50:Z50),A50:Z50,0)

This last formula is where the #VALUE/0! became a problem, because it
classes text as the greatest value (or more specifically, #DIV/0! and
#VALUE/0!). Meaning that by removing anything that isnt a number becomes a
necessity, otherwise you are forced to manually remove them from the arranged
results. The IF(ISERROR is now stopping the text/error codes from progressing
as far as the final tables, so to speak; resulting in =, what at least
appears to be, success!

What say you??

Ted.


"Roger Govier" wrote:



Hi Ted

I'm glad you have got it worked out to your satisfaction.
However, I still believe that intrinsically, all errors should be trapped at
root. Allowing them to pervade through a series of further calculations
before finally trapping them, COULD result in erroneous conclusions.

You have still not said what these earlier formulae are, other than "means,
SD's and the like",and I am sure it would be possible to trap errors at this
level, which MAY have a bearing upon later outcomes.

Still, its your call.

If you are based in the States Ted, enjoy the remainder of Thanksgiving.

Regards

Roger Govier


Ted wrote:


Hi Roger, just to let you know, I have worked it out by using:

=IF(ISERROR(ABS(A50)),"",ABS(A50))

thank you for your help though $;-)

thanks again,
Ted.

"Roger Govier" wrote:




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(G23<G$45,G23G$46),"",IF(G$480,(G 23-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(G23<G$45,G23G$46),"",IF(G$480,(G2 3-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(G23<G$45,G23G$46,G$47<1),"",IF(G$4 80,(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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
The Excel cells are not automatically calc. existing formula. Peggy Martinez Excel Worksheet Functions 2 July 27th 05 07:22 PM
problem with Array Formula OrdOff Excel Worksheet Functions 2 June 30th 05 04:57 PM
Formula works in some cells, doesn't in other Wowbagger New Users to Excel 13 June 30th 05 03:21 PM
trying to create an (almost) circular formula between cells and data validated cells with lists KR Excel Worksheet Functions 0 May 12th 05 07:21 PM
Conditional formatting on cells with a VLOOKUP formula in them JenniM Excel Discussion (Misc queries) 4 April 1st 05 06:45 PM


All times are GMT +1. The time now is 11:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"