jusolson00

Hi everybody, I'm not sure where to post this, so i thought i'd give this a try. I'm having a frustrating time trying to figure this out and I've come up with nothing, so i hope you guys can help. I've been putting together a small data base for my small business and I have set up a spreadsheet with a bunch of values that coorespond to diffent things. The problem is that I've got formulas set up for most of the cells, and while they work great, there is one drawback., the data is entered for years, and are forcasted into the future, so when my formulas forcast into the future, some return with a negative value. I do not want this. I want to be able to tell Excel that when the cell value reaches zero, to keep it at zero and not give me a negative number. I hope i explained this enough. Thanks for any help.

Justin.

cyrnel

This may be too simple but have you tried an if statement?

if(conditional;then;else)

i.e:
=if(a1>0;a1;0)

LewisCouch

You haven't provided an example so it is difficult to answer your question, however; here is a site (http://www.meadinkent.co.uk/datesandtimes.htm) that is a pretty decent reference for Excel functions. Utilizing Excel as a database has it's drawbacks as it is really a spreadsheet tool. A rudimentary database can be created though, and hopefully, the date/time functions you will find in the above link will help you.

__________________
"The gift of liberty is like that of a horse, handsome, strong, and high-spirited. In some it arouses a wish to ride; in many others, on the contrary, it increases the desire to walk."

-- Massimo d'Azeglio

jusolson00

I guess I did try the If function, but found it non helpfull, or i wasnt doing it right. I'll try to give ya an example. the entries i'm interested in are my depreciation list for my business assets., I have it set up so that it calculates the values into the future. well on some assets it uses up the depr. and gives me a zero amount, which i want, and i want to stop there, but then it goes to negative numbers. I suppose i could just edit those functions on those assets so they stop at that point, but i was wondering if there was a condition i could apply to all cells for all the assets to prevent them from calculating a zero amount.

If the "if" function will do this... is it easy to do? I cant seem to get it to work. I guess i dont understand the terminology of sorts. also, the cells that i want to go to zero already have formulas assigned to them, can i still use the "if" function?

okay, well i've played around with the "if" function some more, and it seem to tell me something about circular referance?? also, i can get it to add numbers the cell with the if function, because its adding the "if" function to the orginal function of the cell. I hope i'm explaining this right, thanks again.
Thanks for the help.

cyrnel

This still may be too simple an answer.

Say you have a cell that's computed:

=sum(a1:b4)

Now you want it to only display computed values greater than zero:

=if(sum(a1:b4) > 0;sum(a1:b4);0)
Replace the final zero with "" to display a blank cell instead of zero.

There are other methods but that's the classic double calculation used since Visicalc.

spived2

So basically, If there are formulas in the entries where you do not want the value to go below 0, take the formula you already had, and add it into cyrnel's above formula at the (a1:b4) locations and replace whats in the parenthesis with your formula. You won't lose your numbers, it just kinda filters them so if the values do go below 0, it just shows 0.

__________________

"This visage, no mere veneer of vanity, is it vestige of the vox populi, now vacant, vanished, as the once vital voice of the verisimilitude now venerates what they once vilified. However, this valorous visitation of a by-gone vexation, stands vivified, and has vowed to vanquish these venal and virulent vermin vanguarding vice and vouchsafing the violently vicious and voracious violation of volition. The only verdict is vengeance; a vendetta, held as a votive, not in vain, for the value and veracity of such shall one day vindicate the vigilant and the virtuous. Verily, this vichyssoise of verbiage veers most verbose vis-à-vis an introduction, and so it is my very good honor to meet you and you may call me V." - V

LewisCouch

Sorry, I misunderstood your first post. Having read it more closely, it would be helpful to know what method of depreciation you're using to calculate your values. For example:

The original cost of your asset is $10,000. The salvage value is $5000 after the asset has been fully depreciated. You decide that this asset's useful life is 5 years. The following formula will calculate the depreciation for the fifth year using the sum of years digits depreciation method.

=SYD(cost, salvage, life, period)
=SYD(10000,5000,5,5) returns: $333.33

The conditional method of deriving/assigning values will work for you, however, there may be a superior method when making your initial calculation.

__________________
"The gift of liberty is like that of a horse, handsome, strong, and high-spirited. In some it arouses a wish to ride; in many others, on the contrary, it increases the desire to walk."

-- Massimo d'Azeglio

jusolson00

awesome, I think i finally got it to work, using the If function. The formula bar looks hellish, with all the variables and stuff, ha, BUT IT WORKS!@ Thanks eveybody.
Okay, i have one other small question, when typing in formulas, if say you want to use cell A2 as a constant, so that to change the output of all the formulas, you only have to change A2, how to do you keep that cell constant in the formula when you copy the formula to other cells. So for instance, if i drag the formula to the cell below it, instead of using A2 as a referce, it will change to B2. seems to me i used to use a $ sign in front of the constant cell in school, but here at home that doesnt seem to work, I am using excel 2000 by the way if that makes a difference. Thanks again for the help!

Justin

cyrnel

Yep, a $ sign before the row or column designator will make that part an absolute reference. This is especially useful if you'll be using fill operations to replicate the formula but need part of the forumula to remain fixed by row, column, or both.

a1 vs $a1 vs a$1 vs $a$1

You may want to look into addressing by name to simplify your formulas.