I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.


Many thanks,
Ola.

What values are in N15 & E38? You only appear to have one test for the
INVESTIGATE situation.

Do you want to test the absolute value (Ignoring sign)?

=IF(ABS(N15)$E$38,"INVESTIGATE","fine",)

where E38 = 50,000, N15 =value being compared





"Ola2B" wrote:

I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.


Many thanks,
Ola.

Ola

Try this

=IF( OR(C4 = -50000 , C4< +50000 ), "Investigate" , "Fine" )

Use the OR function deal with the value criteria and if any criteria
is true, the IF Function will return "Investigate"

On Jun 14, 11:42 am, Ola2B wrote:
I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.

Many thanks,
Ola.

I'm not quite clear on what you're trying to do. I'll assume that in
your formula $E$38 is the figure you're testing against, and that it's
a positive number. If you wish to show "INVESTIGATE" when N15 is
either greater than $E$38 or less than the negative of $E$38, change
your formula to:

=IF(OR(N15=$E$38,N15<=-$E$38),"INVESTIGATE","FINE")

Mark Lincoln

On 14 Jun, 16:42, Ola2B wrote:
I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.

Many thanks,
Ola.

In cell E38, the amount is -£50,000 and in cell E39, the amount is +
£50,000. Basically these are variances which i expect a triger. And
that is any variance over and above this amounts should alert me.

Besides, many thanks for all those that have responded so far and will
try all the different suggestions in few minutes.

Ola.

Ola

It sounds like you should be able to do this with an =IF(OR..formula, but
your logic appears wrong
The only condition NOT matched by your stated criteria is if C4 is less
than -50,000.
In other words, the "+50,000 bit" isn't doing anything as it is already
covered by "-50,000)
You probably need something like:
=IF(OR(C4=E38,C4<=E39),"INVESTIGATE","Fine")
With different numbers in E38,E39 (-50000 and 50000 ?)

Clarification needed, I think.

Regards, Phil



"Ola2B" wrote in message
oups.com...
I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.


Many thanks,
Ola.

First point: You asked for something looking at an amount in cell C4, but
your formula looks at N15 and E38. I assume that your £50000 is stored in
E38? Perhaps you can check whether you are looking at C4 or N15?

Second point: Your formula will return FALSE if N15 is equal to E38. I
don't think this is what you intended.

Third point: You talked about "equal to -£50,000 or greater or +£50,000 or
greater". Do you mean less than -£50000 or greater than +£50000? You don't
seem to have tested the negative limit, so perhaps
=IF(OR(N15=$E$38,N15<=-$E$38),"INVESTIGATE","fine")

Fourth point: You gave us the not desperately enlightening information that
it "doesn't seem to be working". It is easier for folk to help you if you
give them a report of what error messages, if any you are seeing, what
result the formula is giving for what input, and what result you expected to
see. There are some clairvoyants on the group, but their crystal balls work
better with some information.

Fifth point: If you are still getting unexpected results, it might be worth
checking that the relevant cells do contain numbers, and not text that looks
like numbers. Try =ISNUMBER(N15) and =ISTEXT(N15) and similarly for E38.
Spaces & non-printing characters can sometimes confuse you.
--
David Biddulph

"Ola2B" wrote in message
oups.com...
I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.


Many thanks,
Ola.

On 14 Jun, 17:37, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
First point: You asked for something looking at an amount in cell C4, but
your formula looks at N15 and E38. I assume that your £50000 is stored in
E38? Perhaps you can check whether you are looking at C4 or N15?

Second point: Your formula will return FALSE if N15 is equal to E38. I
don't think this is what you intended.

Third point: You talked about "equal to -£50,000 or greater or +£50,000 or
greater". Do you mean less than -£50000 or greater than +£50000? You don't
seem to have tested the negative limit, so perhaps
=IF(OR(N15=$E$38,N15<=-$E$38),"INVESTIGATE","fine")

Fourth point: You gave us the not desperately enlightening information that
it "doesn't seem to be working". It is easier for folk to help you if you
give them a report of what error messages, if any you are seeing, what
result the formula is giving for what input, and what result you expected to
see. There are some clairvoyants on the group, but their crystal balls work
better with some information.

Fifth point: If you are still getting unexpected results, it might be worth
checking that the relevant cells do contain numbers, and not text that looks
like numbers. Try =ISNUMBER(N15) and =ISTEXT(N15) and similarly for E38.
Spaces & non-printing characters can sometimes confuse you.
--
David Biddulph

"Ola2B" wrote in message

oups.com...
I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.

Many thanks,
Ola.

You're correct! i think i probably confused myself more that anyone so
far. Let us for now ignore cell n15. I have a variance cell i.e. c4,
now if this cell shows a negative or a positive amount of £50,000 or
more i want the result cell, i.e. cell c5 to come up with
"investigation" otherwise "fine".

What this means is that where the variance in cell c4 is, for example
is +£49,000 or -£49,000, i expect a "fine" result. I hope this
clarifies my request and apologies for earlier confusion.

Thanks.

On Jun 14, 12:14 pm, Ola2B wrote:
On 14 Jun, 16:42, Ola2B wrote:

I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.

Many thanks,
Ola.

In cell E38, the amount is -£50,000 and in cell E39, the amount is +
£50,000. Basically these are variances which i expect a triger. And
that is any variance over and above this amounts should alert me.

Besides, many thanks for all those that have responded so far and will
try all the different suggestions in few minutes.

Ola.

You're not referencing E39 in your example. I'm guessing that's a
typo.

By "over and above" -£50,000 I assume you mean something like -
£55,000. So that you're looking for figures that are greater than
£50,000 or less than -£50,000. Or to put it another way, figures not
between -£50,000 and £50,000.

Try this:

=IF(OR(N15<$E$38,N15$E$39),"INVESTIGATE","FINE")

Mark Lincoln

In that case, following on from my earlier suggestion,
=IF(OR(C4=$E$38,C4<=-$E$38),"INVESTIGATE","fine")
or more simply =IF(ABS(C4)=$E$38,"INVESTIGATE","fine")
--
David Biddulph

On Jun 14, 5:54 pm, Ola2B wrote:

You're correct! i think i probably confused myself more that anyone so
far. Let us for now ignore cell n15. I have a variance cell i.e. c4,
now if this cell shows a negative or a positive amount of £50,000 or
more i want the result cell, i.e. cell c5 to come up with
"investigation" otherwise "fine".

What this means is that where the variance in cell c4 is, for example
is +£49,000 or -£49,000, i expect a "fine" result. I hope this
clarifies my request and apologies for earlier confusion.


On 14 Jun, 17:37, "David Biddulph" <groups [at] biddulph.org.uk
wrote:

First point: You asked for something looking at an amount in cell C4, but
your formula looks at N15 and E38. I assume that your £50000 is stored in
E38? Perhaps you can check whether you are looking at C4 or N15?

Second point: Your formula will return FALSE if N15 is equal to E38. I
don't think this is what you intended.

Third point: You talked about "equal to -£50,000 or greater or +£50,000 or
greater". Do you mean less than -£50000 or greater than +£50000? You don't
seem to have tested the negative limit, so perhaps
=IF(OR(N15=$E$38,N15<=-$E$38),"INVESTIGATE","fine")

Fourth point: You gave us the not desperately enlightening information that
it "doesn't seem to be working". It is easier for folk to help you if you
give them a report of what error messages, if any you are seeing, what
result the formula is giving for what input, and what result you expected to
see. There are some clairvoyants on the group, but their crystal balls work
better with some information.

Fifth point: If you are still getting unexpected results, it might be worth
checking that the relevant cells do contain numbers, and not text that looks
like numbers. Try =ISNUMBER(N15) and =ISTEXT(N15) and similarly for E38.
Spaces & non-printing characters can sometimes confuse you.

"Ola2B" wrote in message

roups.com...
I need a formular that will give "investigate" in a column when a
amount in cell c4 equal to -£50,000 or greater or +£50,000 or greater.
Iam currently using this =IF(N15$E$38,"INVESTIGATE",IF(N15<$E
$38,"fine",FALSE)) which doesn't seem to be working.

Many thanks,
Ola.