display default cell value

  • Thread starter Thread starter jaklouie
  • Start date Start date
J

jaklouie

Is there a way to display a default value in cells? I'm
trying to create a timesheet form for work and I'm having
problems with a "Comp Time" section. The function I've
come up with is the following ... =IF(K3>0,SUM(K3+K4)-K5,IF
(K4>0,SUM(K3+K4)-K5,IF(K5>0,SUM(K3+K4)-K5,0))) but if you
leave a cell "blank" instead of actually putting a "0" in
it then I get "#VALUE!" How can I get K5 to calculate
correctly if k3, k4 or k5 is blank or the value has been
deleted (spaced thru)? Or how do I show "0" as the default
value in those cells. I'm sure it's something simple but
I'm new to excel and I can't find help in the help!
Thanks for any help!!
 
You are getting the VALUE error because the addition and subtraction operators (+ and -) require
that both arguments be numbers or blank cells. They "choke" (as you've found out) on text.

But you should not use addition (+) inside of a SUM formula. It's redundant. That's what the SUM
formula does: add the arguments.

There's also a problem with your checking whether the cell value is greater than 0. If the cell
contains just spaces (or any other text value, for that matter), the result will be TRUE.

I assume that you want the cell value to be treated as 0 if it's blank or contains text. Since
the SUM() function already ignores text and blanks (treats them as 0), your only problem is how
to negate the number in K5 when it may contain text. The N() function says the numeric value of
text is 0. So N() solves that problem.

You can use the SUM formula in conjunction with N() to coerce text in K5 to the number 0, as
Aladin showed.

Or you can write essentially the same thing as =SUM(K3:K4,-N(K5)). Here the "-" is used to
indicate negation, not subtraction. Or you could write =SUM(K3:K4)-SUM(K5) since SUM would treat
text in K5 as 0.

The latter formula is an example (one of the very few) where it makes sense to use SUM with just
one argument.
 
Back
Top