Trouble with formula

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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

 

[Ragdyer]

The only way your formula would return the #VALUE! error is if there's

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

.

 

[Bernard Liengme]

Another possibility is =IF(ISERROR(K6-L6),"",IF(K6<25,25,K6-L6))
best wishes

 

[Guest]

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]

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.

 

[Guest]

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

 

[Ragdyer]

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.

 

[Guest]

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]

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.