I am trying use the following formula in cell M6. =IF(K6<25,25,K6-L6) but it
returns "value!" if K6 is blank. But if K6 is blank I want it to leave the
cell blank. I have tried everything I can think of. Could you help me with
this.

I only get that error when it contains text.

Is K6 really blank?
Does it contain a space, so it only looks like it's blank?

--
Regards,
Ron

"Keith" wrote:

I am trying use the following formula in cell M6. =IF(K6<25,25,K6-L6) but it
returns "value!" if K6 is blank. But if K6 is blank I want it to leave the
cell blank. I have tried everything I can think of. Could you help me with
this.

Amend your formula to test for the blank condition first i.e.

=IF(K6="","",IF(K6<25,25,K6-L6))

The only way your formula would return the #VALUE! error is if there's
something in K6 that you don't see ... like a <space or a null return < ""
from an existing formula.

=IF(K6="","",IF(K6<25,25,K6-L6))

Post back if you *don't* have a formula in K6 that is returning a null< ""
.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Keith" wrote in message
...
I am trying use the following formula in cell M6. =IF(K6<25,25,K6-L6) but
it
returns "value!" if K6 is blank. But if K6 is blank I want it to leave
the
cell blank. I have tried everything I can think of. Could you help me
with
this.

Another possibility is =IF(ISERROR(K6-L6),"",IF(K6<25,25,K6-L6))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Keith" wrote in message
...
I am trying use the following formula in cell M6. =IF(K6<25,25,K6-L6) but
it
returns "value!" if K6 is blank. But if K6 is blank I want it to leave
the
cell blank. I have tried everything I can think of. Could you help me
with
this.

HERE IS THE WHOLE ROW
A6 DATE
B6 FIRST NAME
C6 LAST NAME
D6 CLIENT'S AGE
E6 ADDRESS
F6 CITY
G6 TIME ON
H6 TIME OFF
I6 TOTAL TIME FORMULA =(H6-G6)*1440
J6 RATE FORMULA =IF(I6=0,"
",IF(I6<=G33,H33,IF(I6<=G34,H34,IF(I6<=G35,H35,IF( I6<=G36,H36,H37)))))
K6 CHARGE FORMULA =IF(J6=" "," ",I6*J6)
L6 SENIOR DISCOUNT FORMULA =IF(D664,K6*0.15,"")
M6 TOTAL DUE FORMULA =IF(K6="","",IF(K6<25,25,K6-L6))

THIS ALL WORKS FINE IF D6 IS 65 OR GREATER, HOWEVER IF NOT IT RETURNS #VALUE!

I HAVE BEEN WORKING ON THIS FOR DAYS NOW AND CAN'T GET IT RIGHT. PLEASE
HELP ME.

THANKS
KEITH



"Ragdyer" wrote:

The only way your formula would return the #VALUE! error is if there's
something in K6 that you don't see ... like a <space or a null return < ""
from an existing formula.

=IF(K6="","",IF(K6<25,25,K6-L6))

Post back if you *don't* have a formula in K6 that is returning a null< ""
.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Keith" wrote in message
...
I am trying use the following formula in cell M6. =IF(K6<25,25,K6-L6) but
it
returns "value!" if K6 is blank. But if K6 is blank I want it to leave
the
cell blank. I have tried everything I can think of. Could you help me
with
this.

Your problem is that your formulas return a mixture of text and numbers.
And then you're depending on these returns to perform calculations.

Some formulas return spaces < " " , and others nulls < "" .

For an accurate evaluation, would you complete your scenario by posting
what's in G33 to H37.

In my testing, I produced some zeroes when fudging those values, which
further complicated the issue.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Keith" wrote in message
...
HERE IS THE WHOLE ROW
A6 DATE
B6 FIRST NAME
C6 LAST NAME
D6 CLIENT'S AGE
E6 ADDRESS
F6 CITY
G6 TIME ON
H6 TIME OFF
I6 TOTAL TIME FORMULA =(H6-G6)*1440
J6 RATE FORMULA =IF(I6=0,"
",IF(I6<=G33,H33,IF(I6<=G34,H34,IF(I6<=G35,H35,IF( I6<=G36,H36,H37)))))
K6 CHARGE FORMULA =IF(J6=" "," ",I6*J6)
L6 SENIOR DISCOUNT FORMULA =IF(D664,K6*0.15,"")
M6 TOTAL DUE FORMULA =IF(K6="","",IF(K6<25,25,K6-L6))

THIS ALL WORKS FINE IF D6 IS 65 OR GREATER, HOWEVER IF NOT IT RETURNS
#VALUE!

I HAVE BEEN WORKING ON THIS FOR DAYS NOW AND CAN'T GET IT RIGHT. PLEASE
HELP ME.

THANKS
KEITH



"Ragdyer" wrote:

The only way your formula would return the #VALUE! error is if there's
something in K6 that you don't see ... like a <space or a null return <
""
from an existing formula.

=IF(K6="","",IF(K6<25,25,K6-L6))

Post back if you *don't* have a formula in K6 that is returning a null<
""
.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Keith" wrote in message
...
I am trying use the following formula in cell M6. =IF(K6<25,25,K6-L6)
but
it
returns "value!" if K6 is blank. But if K6 is blank I want it to
leave
the
cell blank. I have tried everything I can think of. Could you help
me
with
this.

G33 $1.08
G34 $1.02
G35 $0.99
G36 $0.90
G37 $0.82

"Ragdyer" wrote:

Your problem is that your formulas return a mixture of text and numbers.
And then you're depending on these returns to perform calculations.

Some formulas return spaces < " " , and others nulls < "" .

For an accurate evaluation, would you complete your scenario by posting
what's in G33 to H37.

In my testing, I produced some zeroes when fudging those values, which
further complicated the issue.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Keith" wrote in message
...
HERE IS THE WHOLE ROW
A6 DATE
B6 FIRST NAME
C6 LAST NAME
D6 CLIENT'S AGE
E6 ADDRESS
F6 CITY
G6 TIME ON
H6 TIME OFF
I6 TOTAL TIME FORMULA =(H6-G6)*1440
J6 RATE FORMULA =IF(I6=0,"
",IF(I6<=G33,H33,IF(I6<=G34,H34,IF(I6<=G35,H35,IF( I6<=G36,H36,H37)))))
K6 CHARGE FORMULA =IF(J6=" "," ",I6*J6)
L6 SENIOR DISCOUNT FORMULA =IF(D664,K6*0.15,"")
M6 TOTAL DUE FORMULA =IF(K6="","",IF(K6<25,25,K6-L6))

THIS ALL WORKS FINE IF D6 IS 65 OR GREATER, HOWEVER IF NOT IT RETURNS
#VALUE!

I HAVE BEEN WORKING ON THIS FOR DAYS NOW AND CAN'T GET IT RIGHT. PLEASE
HELP ME.

THANKS
KEITH



"Ragdyer" wrote:

The only way your formula would return the #VALUE! error is if there's
something in K6 that you don't see ... like a <space or a null return <
""
from an existing formula.

=IF(K6="","",IF(K6<25,25,K6-L6))

Post back if you *don't* have a formula in K6 that is returning a null<
""
.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Keith" wrote in message
...
I am trying use the following formula in cell M6. =IF(K6<25,25,K6-L6)
but
it
returns "value!" if K6 is blank. But if K6 is blank I want it to
leave
the
cell blank. I have tried everything I can think of. Could you help
me
with
this.

I mentioned G33 *TO* H37.
What do you have in the H's?
Those are the important ones, because you're using those to calculate with.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Keith" wrote in message
...
G33 $1.08
G34 $1.02
G35 $0.99
G36 $0.90
G37 $0.82

"Ragdyer" wrote:

Your problem is that your formulas return a mixture of text and numbers.
And then you're depending on these returns to perform calculations.

Some formulas return spaces < " " , and others nulls < "" .

For an accurate evaluation, would you complete your scenario by posting
what's in G33 to H37.

In my testing, I produced some zeroes when fudging those values, which
further complicated the issue.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Keith" wrote in message
...
HERE IS THE WHOLE ROW
A6 DATE
B6 FIRST NAME
C6 LAST NAME
D6 CLIENT'S AGE
E6 ADDRESS
F6 CITY
G6 TIME ON
H6 TIME OFF
I6 TOTAL TIME FORMULA =(H6-G6)*1440
J6 RATE FORMULA =IF(I6=0,"
",IF(I6<=G33,H33,IF(I6<=G34,H34,IF(I6<=G35,H35,IF( I6<=G36,H36,H37)))))
K6 CHARGE FORMULA =IF(J6=" "," ",I6*J6)
L6 SENIOR DISCOUNT FORMULA =IF(D664,K6*0.15,"")
M6 TOTAL DUE FORMULA =IF(K6="","",IF(K6<25,25,K6-L6))

THIS ALL WORKS FINE IF D6 IS 65 OR GREATER, HOWEVER IF NOT IT RETURNS
#VALUE!

I HAVE BEEN WORKING ON THIS FOR DAYS NOW AND CAN'T GET IT RIGHT.
PLEASE
HELP ME.

THANKS
KEITH



"Ragdyer" wrote:

The only way your formula would return the #VALUE! error is if
there's
something in K6 that you don't see ... like a <space or a null
return <
""
from an existing formula.

=IF(K6="","",IF(K6<25,25,K6-L6))

Post back if you *don't* have a formula in K6 that is returning a
null<
""
.
--
HTH,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"Keith" wrote in message
...
I am trying use the following formula in cell M6.
=IF(K6<25,25,K6-L6)
but
it
returns "value!" if K6 is blank. But if K6 is blank I want it to
leave
the
cell blank. I have tried everything I can think of. Could you
help
me
with
this.

SORRY I POSTED IT WRONG

G33 30 H33 $1.08
G34 45 H34 $1.02
G35 60 H35 $0.99
G36 90 H36 $0.90
G37 120 H37 $0.82


"Ragdyer" wrote:

I mentioned G33 *TO* H37.
What do you have in the H's?
Those are the important ones, because you're using those to calculate with.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Keith" wrote in message
...
G33 $1.08
G34 $1.02
G35 $0.99
G36 $0.90
G37 $0.82

"Ragdyer" wrote:

Your problem is that your formulas return a mixture of text and numbers.
And then you're depending on these returns to perform calculations.

Some formulas return spaces < " " , and others nulls < "" .

For an accurate evaluation, would you complete your scenario by posting
what's in G33 to H37.

In my testing, I produced some zeroes when fudging those values, which
further complicated the issue.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Keith" wrote in message
...
HERE IS THE WHOLE ROW
A6 DATE
B6 FIRST NAME
C6 LAST NAME
D6 CLIENT'S AGE
E6 ADDRESS
F6 CITY
G6 TIME ON
H6 TIME OFF
I6 TOTAL TIME FORMULA =(H6-G6)*1440
J6 RATE FORMULA =IF(I6=0,"
",IF(I6<=G33,H33,IF(I6<=G34,H34,IF(I6<=G35,H35,IF( I6<=G36,H36,H37)))))
K6 CHARGE FORMULA =IF(J6=" "," ",I6*J6)
L6 SENIOR DISCOUNT FORMULA =IF(D664,K6*0.15,"")
M6 TOTAL DUE FORMULA =IF(K6="","",IF(K6<25,25,K6-L6))

THIS ALL WORKS FINE IF D6 IS 65 OR GREATER, HOWEVER IF NOT IT RETURNS
#VALUE!

I HAVE BEEN WORKING ON THIS FOR DAYS NOW AND CAN'T GET IT RIGHT.
PLEASE
HELP ME.

THANKS
KEITH



"Ragdyer" wrote:

The only way your formula would return the #VALUE! error is if
there's
something in K6 that you don't see ... like a <space or a null
return <
""
from an existing formula.

=IF(K6="","",IF(K6<25,25,K6-L6))

Post back if you *don't* have a formula in K6 that is returning a
null<
""
.
--
HTH,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"Keith" wrote in message
...
I am trying use the following formula in cell M6.
=IF(K6<25,25,K6-L6)
but
it
returns "value!" if K6 is blank. But if K6 is blank I want it to
leave
the
cell blank. I have tried everything I can think of. Could you
help
me
with
this.

OK, try these formulas.

I added some error trapping to some of them.

I6
=AND(G60,H60)*(H6-G6)*1440

J6
=IF(I6=0,"",IF(I6<=G33,H33,VLOOKUP(I6,G33:H37,2)))

K6
=IF(J6="","",I6*J6)

L6
=IF(AND(D664,K6<""),K6*0.15,0)

M6
=IF(K6="","",IF(K6<25,25,K6-L6))

I believe these will produce what you're looking for.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Keith" wrote in message
...
SORRY I POSTED IT WRONG

G33 30 H33 $1.08
G34 45 H34 $1.02
G35 60 H35 $0.99
G36 90 H36 $0.90
G37 120 H37 $0.82


"Ragdyer" wrote:

I mentioned G33 *TO* H37.
What do you have in the H's?
Those are the important ones, because you're using those to calculate
with.

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Keith" wrote in message
...
G33 $1.08
G34 $1.02
G35 $0.99
G36 $0.90
G37 $0.82

"Ragdyer" wrote:

Your problem is that your formulas return a mixture of text and
numbers.
And then you're depending on these returns to perform calculations.

Some formulas return spaces < " " , and others nulls < "" .

For an accurate evaluation, would you complete your scenario by
posting
what's in G33 to H37.

In my testing, I produced some zeroes when fudging those values,
which
further complicated the issue.
--
Regards,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"Keith" wrote in message
...
HERE IS THE WHOLE ROW
A6 DATE
B6 FIRST NAME
C6 LAST NAME
D6 CLIENT'S AGE
E6 ADDRESS
F6 CITY
G6 TIME ON
H6 TIME OFF
I6 TOTAL TIME FORMULA =(H6-G6)*1440
J6 RATE FORMULA =IF(I6=0,"

",IF(I6<=G33,H33,IF(I6<=G34,H34,IF(I6<=G35,H35,IF( I6<=G36,H36,H37)))))
K6 CHARGE FORMULA =IF(J6=" "," ",I6*J6)
L6 SENIOR DISCOUNT FORMULA =IF(D664,K6*0.15,"")
M6 TOTAL DUE FORMULA =IF(K6="","",IF(K6<25,25,K6-L6))

THIS ALL WORKS FINE IF D6 IS 65 OR GREATER, HOWEVER IF NOT IT
RETURNS
#VALUE!

I HAVE BEEN WORKING ON THIS FOR DAYS NOW AND CAN'T GET IT RIGHT.
PLEASE
HELP ME.

THANKS
KEITH



"Ragdyer" wrote:

The only way your formula would return the #VALUE! error is if
there's
something in K6 that you don't see ... like a <space or a null
return <
""
from an existing formula.

=IF(K6="","",IF(K6<25,25,K6-L6))

Post back if you *don't* have a formula in K6 that is returning
a
null<
""
.
--
HTH,

RD




--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!



--------------------------------------------------------------------------
-
"Keith" wrote in message
...
I am trying use the following formula in cell M6.
=IF(K6<25,25,K6-L6)
but
it
returns "value!" if K6 is blank. But if K6 is blank I want it
to
leave
the
cell blank. I have tried everything I can think of. Could
you
help
me
with
this.