Urgent help on IF please!

[cimbom]

Hi all,
I am trying to come up with a formula in excel. Here it is:

G H I J K
Inventory Orders Safety Production Calculation
45 25 10 -10
(Orders+Safety)-Inventory

What I am trying to do is that whenever the production cell is
negative, It has to be always 0. However, In order to make it 0, the
safety cell should be Safety= Inventory-Orders. So, Safety should be
20 in order for Production to be 0. In the calculation column, I put
=IF(J3<0,I3=E3-G3,0), but that does not work. What formula would you
suggest? I would appreciate your help.

 

[Filo]

The following code should help:

Sub Solve()

'J3=production cell
a = Range("J3").Value

'I3=safety cell
b = Range("I3").Value

If a < 0 Then

Range("I3").Value = b + a * -1
End If


End Sub

 

[FSt1]

hi,
your if formula has errors. remember formulas return values, they cannot
perform actions. i rearranged your formula based on what you said should be
happening.
=IF(J3<0,G3-H3,1)
if Production(j) <0 then Inventory(g)-orders(h) else 0 = 20 in calculation
column.

or did i miss something.

Regards
FSt1

 

[Rick Rothstein \(MVP - VB\)]

I am trying to come up with a formula in excel. Here it is:

G H I J K
Inventory Orders Safety Production Calculation
45 25 10 -10
(Orders+Safety)-Inventory

What I am trying to do is that whenever the production cell is
negative, It has to be always 0.

Does this formula in J3 do what you want?

=--TEXT(H3+I3-G3,"0;\0")

It assumes the numbers in G3, H3 and I3 are always integer values
(non-floating point). If you need to allow for floating point numbers, the
first field in the format specification string will need to be changed to
account for the digits after the decimal point.

Rick

 

[cimbom]

Does this formula in J3 do what you want?

=--TEXT(H3+I3-G3,"0;\0")

It assumes the numbers in G3, H3 and I3 are always integer values
(non-floating point).Ifyou need to allow for floating point numbers, the
first field in the format specification string will need to be changed to
account for the digits after the decimal point.

Rick

Rick,
Thanks for your help. I want a formula in the Calculation formula (K3)
that makes any negative number in column J (290 rows) 0. And to do
this, I have to change the Safety column. If you look at the numbers
up above, Safety should be 20 so that production will be 0.(25+20-45)
So, I am looking for a formula or a macro. I would appreciate your
help. Thanks

 

[Rick Rothstein \(MVP - VB\)]

I am trying to come up with a formula in excel. Here it is:

Rick,
Thanks for your help. I want a formula in the Calculation formula (K3)
that makes any negative number in column J (290 rows) 0. And to do
this, I have to change the Safety column. If you look at the numbers
up above, Safety should be 20 so that production will be 0.(25+20-45)
So, I am looking for a formula or a macro. I would appreciate your
help. Thanks

I don't think I am understanding what you want to do completely yet. I
assumed from your initial posting that you (or your spreadsheet's user) are
filling in values in columns G, H and I and that column J was being
calculated from it (Orders+Safety-Inventory which translates to H+I-G for
each row) and that, whenever any of these row calculations yielded a
negative value, you simply wanted to display a 0 instead. Your latest
posting seems to be saying you want to somehow physically change the value
in your I columns to force the calculation to 0 instead. My proposal does
not require this "forcing of column I to a different value than entered"
solution (nor does it even require the K column that you proposed). If my
first interpretation is correct (that you don't really **need** to change
the values in column I **except** to force column J to 0 wherever it
evaluates to 0), give my formula a try. Here is what I intended you to do
with it. Assuming your original "chart" of values

G H I J
Inventory Orders Safety Production
45 25 10 -10

means that those numbers are in row 3 to start, then place this formula in
J3

=--TEXT(H3+I3-G3,"0;\0")

and copy it down to the end of your data. Doing this will make column J
display either the individual row calculation H+I-G or zero depending on if
that calculation is positive or not. If you don't know how many rows are
going to be involved (or if the number of rows of information will grow with
time), and you don't want to display zeroes for rows where there are no
calculations, you can use this formula instead

=IF(ISNUMBER(I3),--TEXT(H3+I3-G3,"0;\0"),"")

and copy it down well past the last possible row you will ever have to deal
with (or, perhaps, simply copy it down to the bottom of the sheet) and
nothing will be displayed in the J column unless something is entered in the
I column.

Rick

 

[cimbom]

I don't think I am understanding what you want to do completely yet. I
assumed from your initial posting that you (or your spreadsheet's user) are
filling in values in columns G, H and I and that column J was being
calculated from it (Orders+Safety-Inventory which translates to H+I-G for
each row) and that, whenever any of these row calculations yielded a
negative value, you simply wanted to display a 0 instead. Your latest
posting seems to be saying you want to somehow physically change the value
in your I columns to force the calculation to 0 instead. My proposal does
not require this "forcing of column I to a different value than entered"
solution (nor does it even require the K column that you proposed).Ifmy
first interpretation is correct (that you don't really **need** to change
the values in column I **except** to force column J to 0 wherever it
evaluates to 0), give my formula a try. Here is what I intended you to do
with it. Assuming your original "chart" of values

G H I J
Inventory Orders Safety Production
45 25 10 -10

means that those numbers are in row 3 to start, then place this formula in
J3

=--TEXT(H3+I3-G3,"0;\0")

and copy it down to the end of your data. Doing this will make column J
display either the individual row calculation H+I-G or zero depending onif
that calculation is positive or not.Ifyou don't know how many rows are
going to be involved (orifthe number of rows of information will grow with
time), and you don't want to display zeroes for rows where there are no
calculations, you can use this formula instead

=IF(ISNUMBER(I3),--TEXT(H3+I3-G3,"0;\0"),"")

and copy it down well past the last possible row you will ever have to deal
with (or, perhaps, simply copy it down to the bottom of the sheet) and
nothing will be displayed in the J column unless something is entered in the
I column.

Rick- Hide quoted text -

- Show quoted text -

I am sorry. You are right I cannot explain it really well. I can send
you the spreadsheet so that you will have better understanding. I
tried your formula, and it certainly worked. But, it is not what I
want. I dont only want to make all negative numbers in column j 0,
but also, I want a number in the Safety column that makes the
Production column 0. There is no fomula in columns G,H and I. But,
mathematically thinking, in order to make a negative number 0 in
column J, safety = Inv. - Orders, so 45-25=20. As a result, the
production column(J) will be 0 ( 25 + 20 - 45 = 0) So, I first want
to find an appropriate number for safety to make the negative
production number in column J 0. I hope I am clear enough this time.
Thank you very much, Rick.

 

[Rick Rothstein \(MVP - VB\)]

I am sorry. You are right I cannot explain it really well. I can send

you the spreadsheet so that you will have better understanding. I
tried your formula, and it certainly worked. But, it is not what I
want. I dont only want to make all negative numbers in column j 0,
but also, I want a number in the Safety column that makes the
Production column 0. There is no fomula in columns G,H and I. But,
mathematically thinking, in order to make a negative number 0 in
column J, safety = Inv. - Orders, so 45-25=20. As a result, the
production column(J) will be 0 ( 25 + 20 - 45 = 0) So, I first want
to find an appropriate number for safety to make the negative
production number in column J 0. I hope I am clear enough this time.

So I am clear on this... you want to let the user enter a number in the
Safety column and if that number makes the Production calculation negative,
you want the spreadsheet to change it in such a way as to make the
Production value 0... is that correct? If so, then I don't see how you will
do that with a worksheet formula (not unless you can have 2 Safety
columns... one for the "actual" value that the user types in and another for
the "calculated" value that the spreadsheet calculates); rather, it seems
like a macro will be necessary.

Rick