Writing a conditional formula

[clegho5]

I am trying to write a formula for a financial project. The object of my project is when two cells values are compared the lower amount should be eliminated and the larger amount should be reported, therefore the lesser of the two cells should appear in one column and the greater should appear in another. Please Help, I'm all out of ideas and the project request that I compare about 25,000 loans, a formula would be very helpful.

 

[Al Chara]

If you data is in columns A and B starting in row 2 then place this formula in column C for the larger values: =IF(A2>B2=TRUE,A2,B2)
Place this formula in column D for the lesser values:
=IF(A2<B2=TRUE,A2,B2)

Is this what you want?

_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-04-09 15:10

 

[Jay Petrulis]

On 2002-04-09 14:53, clegho5 wrote:
I am trying to write a formula for a financial project. The object of my project is when two cells values are compared the lower amount should be eliminated and the larger amount should be reported, therefore the lesser of the two cells should appear in one column and the greater should appear in another. Please Help, I'm all out of ideas and the project request that I compare about 25,000 loans, a formula would be very helpful.

Hi,

Use the MIN and MAX functions in the respective columns.

HTH,
Jay

 

[g_erhard]

MIN and MAX work great for that sort of thing. They're also useful in that you're not limited to two numbers; they'll return the min or max value from any range.

Something to keep in mind when using MIN... if some of the rows in the range return 0, but you don't want to return 0 as your "minimum" value, you need to exclude those cells from consideration. Otherwise 0 will be the MIN value returned, which may not be what you want (you may want the "next value up from 0).

The way I do that is, in the rows themselves, the formula is set up so that if 0 is the result, return a string or other non-numeric value instead, such as a dash. That way, when your MIN and MAX comparisons are done, no zeros are considered, and string results are skipped.

 

[Aladin Akyurek]

On 2002-04-09 16:00, g_erhard wrote:
MIN and MAX work great for that sort of thing. They're also useful in that you're not limited to two numbers; they'll return the min or max value from any range.

Something to keep in mind when using MIN... if some of the rows in the range return 0, but you don't want to return 0 as your "minimum" value, you need to exclude those cells from consideration. Otherwise 0 will be the MIN value returned, which may not be what you want (you may want the "next value up from 0).

The way I do that is, in the rows themselves, the formula is set up so that if 0 is the result, return a string or other non-numeric value instead, such as a dash. That way, when your MIN and MAX comparisons are done, no zeros are considered, and string results are skipped.

A good tactic, but one that might lead to problems when you need complicated computations regarding the same range.

How about:

=SMALL(A1:D1,COUNTIF(A1:D1,0)+1)

This would give you the MIN of A1:D1, excluding zeroes.

Aladin