Excel Function - Need Help

bacat · 10-18-2002, 02:54 PM

I am tracking fixed asset depletions and need my spreadsheet to subtract monthly depletion from original cost, until the value reaches $0.00 then I want the spreadsheet to reflect "$0.00" instead of a negative number.

What function will sum with the condition that - IF the value is less then .01 display 0?

Please HELP!!!

strangerstill · 10-18-2002, 05:20 PM

You said it! =IF(<formula> < 0, 0, <formula>)

=IF(condition, value_if_true, value_if_false)

bacat · 10-21-2002, 10:02 AM

This will display the words "TRUE" or "FALSE". I need the cell to display a "0", I belive it is a =REPLACE command.

Any ideas....

dunbar · 10-21-2002, 10:26 AM

Replace the terms within the if statement with whatever you want, eg =IF(2>1,"therefore","Danger") will produce therefore in the results. Similarly, =IF(2>1,7,2) will produce 7 as a cell result.

strangerstill · 10-21-2002, 10:28 AM

Er...no...

Quote:

IF
See Also

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Microsoft Excel Help files.

Actually, though, all you really need is to use MIN:
=MIN(<formula>, 0)

Quote:

MIN
See Also

Returns the smallest number in a set of values.

Syntax

MIN(number1,number2,...)

Number1, number2, ... are 1 to 30 numbers for which you want to find the minimum value.

Remarks

You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.

bacat · 10-21-2002, 10:46 AM

Thanks, the "0" is now displaying correctly.

I really appreciate everyones help. I have been stump for a while on this one, just because I couldn't get past what wasn't working.

NEW PROBLEM ....
What if I wanted to replace a value with a word.

Ex: If the sum of the cell was less then 100, display the word ORDER.

strangerstill · 10-21-2002, 12:15 PM

OK, now you do need to use IF.

=IF(<formula> < 100, "ORDER", <formula>)

bacat · 10-21-2002, 01:04 PM

I tried the formula and instead of displaying either "OK" or "ORDER" it displays the entire formula

Ex: =IF(b44/c44>3,"OK",b44/c44<3,"ORDER")

strangerstill · 10-21-2002, 03:53 PM

Er... what you wrote has four arguments: b44/c44>3, "OK", b44/c44<3, and "ORDER". IF only accepts 3 arguments. If you want extra tests, you need to nest IFs (by putting an IF() formula in place of value_if_true or value_if_false)