Without seeing the actual data/information, it is difficult to give the best answer. The concept should work universally.
For example, if cell A1 is negative, and you want to leave cell B1 blank until A1 is positive, you can use a formula like:
=IF(A1<0,"",A1)
Obviously, you'd rework the formula to suit your needs.
It would be kind of complicated. In my workbook, I am dealing with data in time format, imported to cells as text. The reason for this is that the data I am importing is in time format ##:## but does not stop/restart at 24 but continues increasing. This is what I have been using:
Column H1=0:00
Column K2=SUM(J2-H22)
Column M2=SUM(M2-J2)
If K2 is null, I still get the #VALUE! error. I am trying to get blank if there is a null or negative.
Last edited by Eddieduce; 04-23-2007 at 03:22 PM.
Hi,Originally Posted by Eddieduce
If you are dealing with Hours then I presume that you have formatted the cells to [h]:mm:ss to accomodate the 24+ numbers, in which case 'Sum' that includes blank cells should not error.
To avoid an error in M2 use something like
=If(J2>M2,"",sum(M2-J2))
for K2 a similar test.
hth
---
Si fractum non sit, noli id reficere.
IF all you want to do is hide the "#Value!" then try a conditinal format that makes the text the same color as the cell's background.
I know this doesn't answer the formula questin but it will get the job done.
from the menu bar click on FORMAT
then CONDITIONAL FORMATS
then set up your criteria.
if cell
is less than or equal to
00:00
click the format button and select the color of the font if your criteria is met.
This will only work if this is the end of the line for the data you are using. If the results of this cell will be used as part of another cell's formula then this 'fix' won't work for you.
Hope this helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks