The "IF" Statement

[Guest]

i am trying to get a formula to change negative numbers into positive numbers
if they are negative and only on certain documents; these documents have a #
2 or 3 indicating the document types in Column D; The figures that i want to
change into negative if positive and positive if currently negative are in
column G; H; I; & J; The formula that i had typed out is wrong and it goes a
little something like this:
=IF($D7=2, ($G7=$G7*-1; $H7=$H7*-1; $I7=$I7*-1;
$J7=$J7*-1),IF($D7=3,($G7=$G7*-1; $H7=$H7*-1; $I7=$I7*-1; $J7=$J7*-1),
$D7="Null"))

if someone could please point out to me the problem with this? and maybe
suggest a formula to fix this? Please?

 

[Ron Rosenfeld]

i am trying to get a formula to change negative numbers into positive numbers
if they are negative and only on certain documents; these documents have a #
2 or 3 indicating the document types in Column D; The figures that i want to
change into negative if positive and positive if currently negative are in
column G; H; I; & J; The formula that i had typed out is wrong and it goes a
little something like this:
=IF($D7=2, ($G7=$G7*-1; $H7=$H7*-1; $I7=$I7*-1;
$J7=$J7*-1),IF($D7=3,($G7=$G7*-1; $H7=$H7*-1; $I7=$I7*-1; $J7=$J7*-1),
$D7="Null"))

if someone could please point out to me the problem with this? and maybe
suggest a formula to fix this? Please?

A function can only return a value; it cannot change something in some other
cell.

You could have functions in some kind of mirror column or you could use a VBA
macro to accomplish your task; and then run it or have it run depending on a
worksheet or workbook change event.

For the functions you could make your entries in G7:J7 as you have; but then
for display or future computations have your numbers showing in, let us say
AG7:AJ7.

You would then put the formula:

AG7: =IF(OR($D7={2,3}),ABS(G7),G7)

and copy/drag that across to AJ7

For writing a VBA macro, it would be important to know whether the values in
columns G:J are put there by direct entry, or as the result of a formula.




--ron