Formula Help

[Guest]

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 column’s 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.

 

[RagDyer]

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!
==============================================

 

[Guest]

THANK YOU.

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!
==============================================

 

[RagDyer]

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