Writing a conditional formula
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Writing a conditional formula

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.


  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Is this what you want?

    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-09 15:10 ]

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    San Ramon CA
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com