Formula Help
 

Hello,
Using Excel97

I have a spreadsheet with columns week1, week2, difference. Column totals
at bottom.
The difference column has this formula: (week2-week1)

Some week1 an 2 amounts are both negative amounts, so I have this formula to
Adjust for that: =IF(AND(C2<0,B2<0),(C2-B2)*-1)

Question 1 : is there a way to write a formula that will handle the majority
of the amounts which are positive in both columns and adjust for when they
are both negative amounts?

Week1 Week2 difference
1,234 5698 4,467
-5698 -1234 4,467


Question 2: Sometimes the total at the bottom of one of the columns may be
negative, while the other column maybe be positive. Will the formula here
need to be done manually when this situation arises, or is there a better way
to handle it?
Example: subtotWeek1 subtotWeek2 difference
1,234 -5698 week2-week1
(would yield an incorrect result)

Thanks for any Help.

Formula Help
 

Try this for your subtraction:

=ABS(C2)-ABS(B2)

And try these *array* formulas to total your week1 and week2 columns:

=SUM(ABS(B1:B50))
=SUM(ABS(C1:C50))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"JHL" wrote in message
...
Hello,
Using Excel97

I have a spreadsheet with columns week1, week2, difference. Column totals
at bottom.
The difference column has this formula: (week2-week1)

Some week1 an 2 amounts are both negative amounts, so I have this formula
to
Adjust for that: =IF(AND(C2<0,B2<0),(C2-B2)*-1)

Question 1 : is there a way to write a formula that will handle the
majority
of the amounts which are positive in both columns and adjust for when they
are both negative amounts?

Week1 Week2 difference
1,234 5698 4,467
-5698 -1234 4,467


Question 2: Sometimes the total at the bottom of one of the columns may
be
negative, while the other column maybe be positive. Will the formula here
need to be done manually when this situation arises, or is there a better
way
to handle it?
Example: subtotWeek1 subtotWeek2 difference
1,234 -5698 week2-week1
(would yield an incorrect result)

Thanks for any Help.

Formula Help
 

THANK YOU.

"RagDyer" wrote:

Try this for your subtraction:

=ABS(C2)-ABS(B2)

And try these *array* formulas to total your week1 and week2 columns:

=SUM(ABS(B1:B50))
=SUM(ABS(C1:C50))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"JHL" wrote in message
...
Hello,
Using Excel97

I have a spreadsheet with columns week1, week2, difference. Column totals
at bottom.
The difference column has this formula: (week2-week1)

Some week1 an 2 amounts are both negative amounts, so I have this formula
to
Adjust for that: =IF(AND(C2<0,B2<0),(C2-B2)*-1)

Question 1 : is there a way to write a formula that will handle the
majority
of the amounts which are positive in both columns and adjust for when they
are both negative amounts?

Week1 Week2 difference
1,234 5698 4,467
-5698 -1234 4,467


Question 2: Sometimes the total at the bottom of one of the columns may
be
negative, while the other column maybe be positive. Will the formula here
need to be done manually when this situation arises, or is there a better
way
to handle it?
Example: subtotWeek1 subtotWeek2 difference
1,234 -5698 week2-week1
(would yield an incorrect result)

Thanks for any Help.

Formula Help
 

You're welcome ... and thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JHL" wrote in message
...
THANK YOU.

"RagDyer" wrote:

Try this for your subtraction:

=ABS(C2)-ABS(B2)

And try these *array* formulas to total your week1 and week2 columns:

=SUM(ABS(B1:B50))
=SUM(ABS(C1:C50))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"JHL" wrote in message
...
Hello,
Using Excel97

I have a spreadsheet with columns week1, week2, difference. Column
totals
at bottom.
The difference column has this formula: (week2-week1)

Some week1 an 2 amounts are both negative amounts, so I have this
formula
to
Adjust for that: =IF(AND(C2<0,B2<0),(C2-B2)*-1)

Question 1 : is there a way to write a formula that will handle the
majority
of the amounts which are positive in both columns and adjust for when
they
are both negative amounts?

Week1 Week2 difference
1,234 5698 4,467
-5698 -1234 4,467


Question 2: Sometimes the total at the bottom of one of the columns
may
be
negative, while the other column maybe be positive. Will the formula
here
need to be done manually when this situation arises, or is there a
better
way
to handle it?
Example: subtotWeek1 subtotWeek2 difference
1,234 -5698 week2-week1
(would yield an incorrect result)

Thanks for any Help.