Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel 2007 scripting issue..

Apr 23, 2008 12:48AM PDT

I am writing an excel sheet to help me keep track of printer toner needs around the office. I am using Excel 2007. For each type of toner we have, i list how many printers we have using it and how many toners we have on hand. The value of the printers using the toner is subtracted from the on hand value and reflected in a cell using this formula: =IMSUB(D4,D5) Where D4 is the number of printers using the toner and D5 is the number of toners on hand. The problem is, that when the number of toners on hand is larger than the number of printers using it, the value ends up being a negative number. I am looking for an IF then else argument to use that would display a 0 instead of any negative number. so instead of the sheet saying that i need to order negative 1 toners, it would say that i need to order 0 :o) .

I have tried many different things, nothing works.
ANY help would be greatly appreciated! Thank YOU!
-Adam S.

Discussion is locked

- Collapse -
Re: Excel
Apr 23, 2008 1:14AM PDT

It's not exactly the right forum, this. If you look at the header above it on http://forums.cnet.com/?tag=bc you'll this is a forum about developing software for the web. A few lines higher you'll find the Office and Productivity forum, and that's were most Excel questions are coming.

And it isn't really called scripting also. Scripting for a software engineer is a rather specific term, and formulas and even macros in Excel are not called scripting. For macros everything beyond recording is called 'programming' and writing formulas is just, well, called writing formulas.

And your problem is just a formula, and rather easy one also. Choose the one you like most from:
=MAX (0,value)
and
=IF (value>0,value,0)
and
=IF (value>=0,value,0)

I tested these on Excel XP. But I don't think there will be problems in Excel 2007 with them.

For future questions on Excel, please choose the Office forum.


Kees

- Collapse -
Seems to be more complicated
Apr 23, 2008 2:35AM PDT

Thanks for your response!

I took one of your formulas and put it to use, However Excel says that it contains an error. Perhaps you can see where i am going wrong.
Here is my line:
=IF(IMSUB(D4,D5)>=0,IMSUB(D4,D5),0)

Thanks Very Much!
-Adam

- Collapse -
Re: IMSUB
Apr 24, 2008 5:12AM PDT

You learned me something: the function IMSUB.

http://office.microsoft.com/en-us/excel/HP052091341033.aspx shows it calculates the difference of two complex numbers, like in =IMSUB("13+4i","5+3i"). That's 8+i, of course.
And, indeed, complex numbers can't be compared with 'normal' numbers (scalars), so it's quite correct that Excel gives an error message.

Can you explain why you need complex numbers (as you might know, i is the non existing square root of -1) for the task of keeping track of the toner stock? If you can't, maybe review the math of your calculations.

Kees

- Collapse -
I agree about IMSUB
Apr 24, 2008 5:48AM PDT

It's an engineering function and would not appear to be appropriate in this context.

What you seem to be doing is an inventory, but when you say "How many toners on hand", what do you mean? If you are doing a count of your stock for re-ordering purposes, you need to know how many printers are using the different types of toners, and how many 'spare' toners you have in stock.

For myself I would use a simpler approach and have just 4 columns; If the first toner was Toner A, then

Col 1 = Toner type
Col 2 = How many printers use this toner
Col 3 = How many toners in stock, (ie, not being used)
Col 4 = How many toners of this type in total. (=SUM of the cells in col 2 and 3 for that row where toner A is listed).

Repeat in the next row for each type of toner.

Then at the bottom of each row is a simple total of the row. And you can manipulate those totals in any way you need.

Isn't that how it would work?

Mark

- Collapse -
"At the bottom of each row"?
Apr 24, 2008 6:00AM PDT

The bottom of each column, maybe?

It might be nice to add a fifth column, like the col 3 divided by col 2 (or by col 4), shown as percentage. A simple (maybe too simple) criterion to reorder could be something like 'less than 20% spare'. Then in the col 6 and 7 you could note date and amount ordered (and clear those cells when they arrive, and the spares would be 120% again, depending on how many you ordered).

Just an idea. But I agree, keep it simple!

Kees