Trouble with formula

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" <Keith@discussions.microsoft.com> wrote in message
news:77BF9398-29F0-4AB8-A330-8C47EBCA36AD@microsoft.com...
> 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(D6>64,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" <Keith@discussions.microsoft.com> wrote in message
> news:77BF9398-29F0-4AB8-A330-8C47EBCA36AD@microsoft.com...
> > 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" <Keith@discussions.microsoft.com> wrote in message
news:F8E8BB31-E50D-4CBF-913F-AFE1E9693676@microsoft.com...
> 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(D6>64,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" <Keith@discussions.microsoft.com> wrote in message
> > news:77BF9398-29F0-4AB8-A330-8C47EBCA36AD@microsoft.com...
> > > 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" <Keith@discussions.microsoft.com> wrote in message
> news:F8E8BB31-E50D-4CBF-913F-AFE1E9693676@microsoft.com...
> > 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(D6>64,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" <Keith@discussions.microsoft.com> wrote in message
> > > news:77BF9398-29F0-4AB8-A330-8C47EBCA36AD@microsoft.com...
> > > > 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" <Keith@discussions.microsoft.com> wrote in message
news:4BC84BE0-799A-470F-B4D7-2A7E58A336DC@microsoft.com...
> 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" <Keith@discussions.microsoft.com> wrote in message
> > news:F8E8BB31-E50D-4CBF-913F-AFE1E9693676@microsoft.com...
> > > 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(D6>64,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" <Keith@discussions.microsoft.com> wrote in message
> > > > news:77BF9398-29F0-4AB8-A330-8C47EBCA36AD@microsoft.com...
> > > > > 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" <Keith@discussions.microsoft.com> wrote in message
news:77BF9398-29F0-4AB8-A330-8C47EBCA36AD@microsoft.com...
>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.