I need help in setting up a formula based upon a value. What i need is, if
the value of a specific cell is between -49 - 29, i need an "OK" in another
cell.
So, if the value of (A3) is 12, then i want an "OK" to automatically come up
in (A4).
Is this do-able? I know you can do "IF" formulas, but I cant find one that
fits.
Also, is there a way to turn a formula off if theres no value within a cell.
For example, i have a column titled "Bid" , one titled "Actual Amount" and
one titled "Difference." before the actual amount comes in, i leave the
cells blank, but i already have the "bid" amount and the formula specified in
"Actual Amount". So, i get a VALUE!# error message in my "Actual Amount"
cell.
I hope this makes sense. lol

Hello Jake,

You need a combination AND and IF statement:

=IF(AND(A3=-49,A3<=-29,A3<""),"OK","")

If you have any questions with it email back!

Judith


--
JudithJubilee
------------------------------------------------------------------------
JudithJubilee's Profile: http://www.excelforum.com/member.php...fo&userid=6882
View this thread: http://www.excelforum.com/showthread...hreadid=518721

=IF(AND(A1=-49,A1<=-29),"ok","")

=IF(A4=12,"OK","")

Wrap your formula in an Error Trap, as......

=IF(ISERR(YourFormula,"",YourFormula)

hth
Vaya con Dios,
Chuck, CABGx3



"Jake" wrote:

I need help in setting up a formula based upon a value. What i need is, if
the value of a specific cell is between -49 - 29, i need an "OK" in another
cell.
So, if the value of (A3) is 12, then i want an "OK" to automatically come up
in (A4).
Is this do-able? I know you can do "IF" formulas, but I cant find one that
fits.
Also, is there a way to turn a formula off if theres no value within a cell.
For example, i have a column titled "Bid" , one titled "Actual Amount" and
one titled "Difference." before the actual amount comes in, i leave the
cells blank, but i already have the "bid" amount and the formula specified in
"Actual Amount". So, i get a VALUE!# error message in my "Actual Amount"
cell.
I hope this makes sense. lol

Thanks CLR and Judith. The first two formulas work perfectly.

about the error trap, im getting errors left and right. or, TRUE is
filling. i just want a blank cell instead of TRUE. is that possible?
here are my two current formulas that need blanks if data is not the

=ISERR(IF(AND(J31=-49.99,J31<=29.99,J31<""),"OK",""))
i get a TRUE statement on that. i just want blank.

=ISERR(SUM(F31-G31))
i get a TRUE statement on that. i just want blank.


thanks again everyone,
and thanks in advance too.
:)



"CLR" wrote:

=IF(AND(A1=-49,A1<=-29),"ok","")

=IF(A4=12,"OK","")

Wrap your formula in an Error Trap, as......

=IF(ISERR(YourFormula,"",YourFormula)

hth
Vaya con Dios,
Chuck, CABGx3



"Jake" wrote:

I need help in setting up a formula based upon a value. What i need is, if
the value of a specific cell is between -49 - 29, i need an "OK" in another
cell.
So, if the value of (A3) is 12, then i want an "OK" to automatically come up
in (A4).
Is this do-able? I know you can do "IF" formulas, but I cant find one that
fits.
Also, is there a way to turn a formula off if theres no value within a cell.
For example, i have a column titled "Bid" , one titled "Actual Amount" and
one titled "Difference." before the actual amount comes in, i leave the
cells blank, but i already have the "bid" amount and the formula specified in
"Actual Amount". So, i get a VALUE!# error message in my "Actual Amount"
cell.
I hope this makes sense. lol

ok, nevermind. i got it now.
thanks all.

these boards need an "Edit" feature. lol


"Jake" wrote:

Thanks CLR and Judith. The first two formulas work perfectly.

about the error trap, im getting errors left and right. or, TRUE is
filling. i just want a blank cell instead of TRUE. is that possible?
here are my two current formulas that need blanks if data is not the

=ISERR(IF(AND(J31=-49.99,J31<=29.99,J31<""),"OK",""))
i get a TRUE statement on that. i just want blank.

=ISERR(SUM(F31-G31))
i get a TRUE statement on that. i just want blank.


thanks again everyone,
and thanks in advance too.
:)



"CLR" wrote:

=IF(AND(A1=-49,A1<=-29),"ok","")

=IF(A4=12,"OK","")

Wrap your formula in an Error Trap, as......

=IF(ISERR(YourFormula,"",YourFormula)

hth
Vaya con Dios,
Chuck, CABGx3



"Jake" wrote:

I need help in setting up a formula based upon a value. What i need is, if
the value of a specific cell is between -49 - 29, i need an "OK" in another
cell.
So, if the value of (A3) is 12, then i want an "OK" to automatically come up
in (A4).
Is this do-able? I know you can do "IF" formulas, but I cant find one that
fits.
Also, is there a way to turn a formula off if theres no value within a cell.
For example, i have a column titled "Bid" , one titled "Actual Amount" and
one titled "Difference." before the actual amount comes in, i leave the
cells blank, but i already have the "bid" amount and the formula specified in
"Actual Amount". So, i get a VALUE!# error message in my "Actual Amount"
cell.
I hope this makes sense. lol

That's ok Jake, as someone told me once "we seem to get a lot smarter just as
soon as we press the send button <g".......

Glad you got it working, and thanks for the feedback.......

Vaya con Dios,
Chuck, CABGx3



"Jake" wrote:

ok, nevermind. i got it now.
thanks all.

these boards need an "Edit" feature. lol


"Jake" wrote:

Thanks CLR and Judith. The first two formulas work perfectly.

about the error trap, im getting errors left and right. or, TRUE is
filling. i just want a blank cell instead of TRUE. is that possible?
here are my two current formulas that need blanks if data is not the

=ISERR(IF(AND(J31=-49.99,J31<=29.99,J31<""),"OK",""))
i get a TRUE statement on that. i just want blank.

=ISERR(SUM(F31-G31))
i get a TRUE statement on that. i just want blank.


thanks again everyone,
and thanks in advance too.
:)



"CLR" wrote:

=IF(AND(A1=-49,A1<=-29),"ok","")

=IF(A4=12,"OK","")

Wrap your formula in an Error Trap, as......

=IF(ISERR(YourFormula,"",YourFormula)

hth
Vaya con Dios,
Chuck, CABGx3



"Jake" wrote:

I need help in setting up a formula based upon a value. What i need is, if
the value of a specific cell is between -49 - 29, i need an "OK" in another
cell.
So, if the value of (A3) is 12, then i want an "OK" to automatically come up
in (A4).
Is this do-able? I know you can do "IF" formulas, but I cant find one that
fits.
Also, is there a way to turn a formula off if theres no value within a cell.
For example, i have a column titled "Bid" , one titled "Actual Amount" and
one titled "Difference." before the actual amount comes in, i leave the
cells blank, but i already have the "bid" amount and the formula specified in
"Actual Amount". So, i get a VALUE!# error message in my "Actual Amount"
cell.
I hope this makes sense. lol

Is there a way to error trap a formula without writing the formula 2 times,
the formula's are getting very long and hard to read / understand

--
Shared knowledge makes you smarter


"CLR" wrote:

=IF(AND(A1=-49,A1<=-29),"ok","")

=IF(A4=12,"OK","")

Wrap your formula in an Error Trap, as......

=IF(ISERR(YourFormula,"",YourFormula)

hth
Vaya con Dios,
Chuck, CABGx3



"Jake" wrote:

I need help in setting up a formula based upon a value. What i need is, if
the value of a specific cell is between -49 - 29, i need an "OK" in another
cell.
So, if the value of (A3) is 12, then i want an "OK" to automatically come up
in (A4).
Is this do-able? I know you can do "IF" formulas, but I cant find one that
fits.
Also, is there a way to turn a formula off if theres no value within a cell.
For example, i have a column titled "Bid" , one titled "Actual Amount" and
one titled "Difference." before the actual amount comes in, i leave the
cells blank, but i already have the "bid" amount and the formula specified in
"Actual Amount". So, i get a VALUE!# error message in my "Actual Amount"
cell.
I hope this makes sense. lol

Upgrade to xl2007.

It added a new =iferror() function.

Excel ESG wrote:

Is there a way to error trap a formula without writing the formula 2 times,
the formula's are getting very long and hard to read / understand

--
Shared knowledge makes you smarter

"CLR" wrote:

=IF(AND(A1=-49,A1<=-29),"ok","")

=IF(A4=12,"OK","")

Wrap your formula in an Error Trap, as......

=IF(ISERR(YourFormula,"",YourFormula)

hth
Vaya con Dios,
Chuck, CABGx3



"Jake" wrote:

I need help in setting up a formula based upon a value. What i need is, if
the value of a specific cell is between -49 - 29, i need an "OK" in another
cell.
So, if the value of (A3) is 12, then i want an "OK" to automatically come up
in (A4).
Is this do-able? I know you can do "IF" formulas, but I cant find one that
fits.
Also, is there a way to turn a formula off if theres no value within a cell.
For example, i have a column titled "Bid" , one titled "Actual Amount" and
one titled "Difference." before the actual amount comes in, i leave the
cells blank, but i already have the "bid" amount and the formula specified in
"Actual Amount". So, i get a VALUE!# error message in my "Actual Amount"
cell.
I hope this makes sense. lol


--

Dave Peterson

Note, though, that iferror() doesn't discriminate. If you're using it to
trap a specific error, for instance, the #N/A returned by an
unsuccessful VLOOKUP(), it will happily give the value_if_error value if
there are #VALUE!, #NAME!, or #REF! errors returned by VLOOKUP() as well.


In article ,
Dave Peterson wrote:

Upgrade to xl2007.

It added a new =iferror() function.