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.