Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Issue with editing Excel formula

    I am obviously missing something here. I am trying to edit a relitivly simple formula and excel is rejecting the change with a #VALUE error. =MIN(IF(I637:AJ637=0,"",I637:AJ637)) The formula simply looks up the minimum value in the range. The IF portion is just to check for zero (0) values. The formula as stated above works perfectly. The error pops up when i try to extend the range to column BR to accomidate new added comomns.

    I would have expected this to be stright forward -- either double click and drag the range to the new column or simply edit in the cell itself. Any time I make any change to the formula it automaticly rejects.

    Any advice / suggestions would be appreceated.

    Thanks,

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It is an array formula. It needs to be confirmed with with ctrl-shift-enter. It will give a #Value if confirmed only with enter.

    Steve

  3. #3
    New Lounger
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the suggestion but unfortionatly control - shift - enter did not work. To give more information on the issue: I am using excel 2010. when i mouse over the side tab on the error cell it give the message: A value used in the formula is of the wrong data type. all of the related cells are formated for currency with 2 decimal places.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are you sure all the numbers are actually numbers? Remove any alignment from the cells. Text will be left-aligned and numbers right-aligned. [The format is immaterial]

    Steve

  5. #5
    New Lounger
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes, all the numbers are numbers -- everything is right alligned. I have set format for both accounting and currency. Both work fine for the origional formula but beaks anytime i try to add in / extend to the additional collums.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are you maintaining both array ranges as the same size?
    [could you attach a sample file demonstrating the problem, you can remove proprietary info]
    Steve

  7. #7
    New Lounger
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i have attached a highly abridged version of the workbook (with price and rates changed ). I do appreceate the help, I have been using Excel for longer then i care to remember. Heck, i have been using spreadsheets even longer then that going back to Lotus123 LOL. This one has me stumped though.
    Attached Files Attached Files

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Robert,

    I got it to work with this formula:
    =MIN(IF(I17:BR17>0,I17:BR17))
    Note: This is an array formula and you MUST press CTRL+Shift+Enter to activate it as such.
    MinArray.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    RobertPeterson (2013-06-28)

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As RetiredGeek indicated, the formula in F17 in your attached file was confirmed with enter, not ctrl-shift-enter. When entered as an array (with ctrl-shift-enter) the value is displayed

    [when confirmed correctly, in the formula bar the formula will displayed with brackets like:
    {=MIN(IF(I17:BR17=0,"",I17:BR17))}
    Note: the brackets are not entered, excel just displays them as an indicator]

    Steve
    PS:
    i have been using spreadsheets even longer then that going back to Lotus123
    I worked with "Appleworks" which had a spreadsheet, word processor, and database on an Apple IIc in the mid-1980s when I was finalizing my PhD. When I started work in 1986, I moved up to Lotus123. I did not start using Excel until about 1997

    PPS it is Praire du Chien Wisconsin, not Praire du Chein Wisconsin

    PPPS: You could use the slightly shorter formula. You don't need the extra condition:
    =MIN(IF(I17:BR17<>0,I17:BR17))
    Your formula in G17 (and others in G) can be shortened and the IF eliminated by using:
    =MAX(F17,$J$6)
    Last edited by sdckapr; 2013-06-28 at 14:35.

  11. #10
    New Lounger
    Join Date
    Jun 2013
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That did the trick. Thank you SOO much . it also helps that i remembered that the cont / shift / enter has to be done while you are still in edit mode LOL. I have see this before but its been a long while LOL. So thanks again.

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That was the first suggestion told to you and you insisted that you had done this...

    Steve

Posting Permissions

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