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.

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.

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.

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.

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.

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.