Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm new to array formulas and am trying out some simple calculations (Excel 2003). Trying to average values in column A if corresponding value in column B ="Y". So,

    AVERAGE(IF(B6:B80="Y",A6:A80)) -- Control+Shift+Enter.

    Problem is it only works if I select more than one cell to output the results into. If I try the formula in just cell, Excel doesn't recognize it as an array formula.

    What am I missing? Do I need to change any of my Excel settings for it to let me do these formulas in a single cell?

    Many thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Excel should always accept an array formula in a single cell.

    Er, do you happen to have ticked the check box "Transition formula evaluation" in the Transition tab of Tools | Options...? That would change the result of the formula.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This worked for me, I'm using 2007.

    AVERAGEIF(B6:B80,"Y",A6:A80)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='stans' post='782665' date='01-Jul-2009 23:11']This worked for me, I'm using 2007.

    AVERAGEIF(B6:B80,"Y",A6:A80)[/quote]
    But AVERAGEIF is new in Excel 2007, it didn't exist yet in Excel 2003 (and before).

  5. #5
    New Lounger
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782663' date='01-Jul-2009 17:05']Welcome to the Lounge!

    Excel should always accept an array formula in a single cell.

    Er, do you happen to have ticked the check box "Transition formula evaluation" in the Transition tab of Tools | Options...? That would change the result of the formula.[/quote]

    Thanks! Answer is, no, those check boxes are all unchecked. This is happening on any array formula I try...I have to select multiple output cells first.

    Any other ideas? I'm at my wits end trying to resolve something so simple.

    P.S. Unfortunately I'm stuck with Excel 2003.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='gabba' post='782668' date='01-Jul-2009 23:18']Any other ideas? I'm at my wits end trying to resolve something so simple.[/quote]
    This is definitely not "normal" behavior. Try Jan Karel Pieterse's Systematic Approach to behavioral Problems in XL.

  7. #7
    New Lounger
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782670' date='01-Jul-2009 17:21']This is definitely not "normal" behavior. Try Jan Karel Pieterse's Systematic Approach to behavioral Problems in XL.[/quote]

    Ok...thanks for confirming that I'm not crazy at least.

    Not to get too stupidly obvious, but you select a cell, type in formula in the formula bar and then C-S-E, right? Is there an Excel setting that controls how cell selection works? This probably makes no sense, but I thought I'd ask.

    Thanks.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='gabba' post='782673' date='01-Jul-2009 23:32']Not to get too stupidly obvious, but you select a cell, type in formula in the formula bar and then C-S-E, right?[/quote]
    Yes, indeed, and I'm not aware of any setting in Excel that would disable this.

  9. #9
    New Lounger
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='782675' date='01-Jul-2009 17:40']Yes, indeed, and I'm not aware of any setting in Excel that would disable this.[/quote]
    Sir, you are a genius. My XlStart folder under App Data was empty, but the other one wasn't...I emptied the XlStart folder under Program files and started Excel up...it works now.

    Good grief. This wasted way too much of my time.

    Many, many thanks!!!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Great! (Thanks go to our moderator Pieterse)

Posting Permissions

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