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.

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.

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.
> > > >
> > > >
> >
> >

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" <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.
> > > > >
> > > > >
> > >
> > >
>
>

OK, try these formulas.

I added some error trapping to some of them.

I6
=AND(G6>0,H6>0)*(H6-G6)*1440

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

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

L6
=IF(AND(D6>64,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" <Keith@discussions.microsoft.com> wrote in message
news:A0C99F9D-C90A-48EB-9563-177128AF6233@microsoft.com...
> 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" <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.
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >