Formula with negative amount

  • Thread starter Thread starter Roxy
  • Start date Start date
R

Roxy

I have a formula that currently reads like this
=IF(C45>C47,C45,C47)

I want to now be able to have it include the fact that either one of the
cells could be a negative amount and pull that number. Would that be
possible?

Thank you in advance for any help.

Roxy
 
These are all good, but now I have another problem. IF C47 is greater then
C45 then it still only is pulling the amt from C45??? Any ideas? Thanks
everyone!!

Roxy
 
Roxy,

A quick scan though all the solutions shows that everyones formula returns
the max of C45 & C47 if they are both positive and the minimum of thos cells
iif either or both are negative so Im confused. Please provide what answer
you expect to the options

C45 C47
1 2=
2 1=
-1 1=
1 -1=
-1 -1=
-1 -2=
-2 -1=


Mike
 
I was just told I am good to go with the way you have shown me. No need to
worry about any thing else. So thank you so very much. Sorry for any extra
confusion. :)

~Roxy
 
Roxy said:
These are all good, but now I have another problem.
IF C47 is greater then C45 then it still only is pulling
the amt from C45??? Any ideas?
Click to expand...

Based on the fact that you were basically pleased with Dave's solution (and
others that were similar) and on the fact that your original formula did not
do what you want, I presume that if the sign of both values is the same, you
want the larger __magnitude__. For example, -45 is larger than -40 in
magnitude, even though -45 is less than -40; and 45 is larger than 40 in
magnitude).

What is unclear is: which do you want if one cell has -45 and the other has
40?

If you want -45 in that case, then you always want the larger in magnitude,
regardless of sign.

One solution:

=if(abs(C45) > abs(C47), C45, C47)

If you want 40 in that case, then you only want the smaller negative number
(larger in magnitude) when both numbers are negative.

One solution:

=if(max(C45,C47)<0, min(C45,C47), max(C45,C47))

Try the appropriate solution with the following combinations:

-45 40
-40 45
-45 -40
45 40

If you do not like all of the results, please post back with __all__ of the
expected results, not just the ones you don't like.
 
Back
Top