Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide zeros & negatives for date formulas
I am working a data sheet with start/finish dates from which the bosses want
charts made etc. If I don't have a finish date, I get a massive negative number, which skews everything. Plus the formula I am using for working hours leaves me with a "-8" in every form block that has not yet had data entered. Is there a way to suppress these negative numbers without affecting format? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide zeros & negatives for date formulas
you could wrap your formulas like this to output 0 instead of negative numbers
=Max(formula,0) or this will output a blank space (which is ignored by SUM and AVERAGE) =IF(formula<0,"",formula) "kimdnw" wrote: I am working a data sheet with start/finish dates from which the bosses want charts made etc. If I don't have a finish date, I get a massive negative number, which skews everything. Plus the formula I am using for working hours leaves me with a "-8" in every form block that has not yet had data entered. Is there a way to suppress these negative numbers without affecting format? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hide zeros & negatives for date formulas
OK, so my existing formula is:
=(NETWORKDAYS(Start,Finish, Holiday)-1)*8+(MOD(D3,1)-MOD(C3,1))*24 based on an 8 hour work day and a 24 hour clock New formula is: =MAX((NETWORKDAYS(C3,D3, A44:A60)-1)*8+(MOD(D3,1)-MOD(C3,1))*24,0) Seems to work! Thanks Sloth! Kimdnw "Sloth" wrote: you could wrap your formulas like this to output 0 instead of negative numbers =Max(formula,0) or this will output a blank space (which is ignored by SUM and AVERAGE) =IF(formula<0,"",formula) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating formulas that allow the solutions to start with zeros. | Excel Discussion (Misc queries) | |||
How can I hide formulas in a spreadsheet to send to a customer? | Excel Discussion (Misc queries) | |||
How do I hide formulas in Excel cells? | Excel Discussion (Misc queries) | |||
I want to hide the formula's which I have done, so that no one ca. | Excel Discussion (Misc queries) | |||
is there a way to hide zeros in a line graph | Charts and Charting in Excel |