Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to define a function that take the MIN of the values in every other column in a range of columns on a row...did that make any sense? In other words, I want to do something like MIN(G4, I4, K4...W4), but I dont want to have to list all the intermediat column addreses in the list. How would I go about doing this in the formula? FWIW, over time the upper limit of the range will be changing too.

    Thanks.
    Bill Lugg

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [attachment=89440:MinOdds.jpg]

    But these are ARRAY Formulas so you must submit them using

    SHIFT CTRL ENTER as a combined key press

    With changes to the MOD Divisor, you can adjust this for every 3rd, 4th etc
    The version you use depends upon whether you want 1st, 3rd, 5th or 2nd, 4th, 6th etc

    You could also adapt to use with Rows using the ROW Function

    and with MAX as long as you use MIN(Range)-1 in the place of Max(Range)+1

    Also, if you fix the starting column letter eg: $A, then you could drag it to the right
    as the range grows.
    Attached Images Attached Images
    Andrew

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Andrew,

    Nice Job once again!

    The more I see the use of Array Formulas the madder I get that I didn't know about this functionality while I was still working.

    Just goes to show you you CAN teach an Old Dog new tricks!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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