Consulting

Results 1 to 5 of 5

Thread: ReDo formula Help

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Oct 2010
    Posts
    73
    Location

    ReDo formula Help

    Hi,

    I thought the problem was solved in my post
    http://www.vbaexpress.com/forum/showthread.php?t=36992
    I'm attaching the workbook to see if this issue can be fixed.
    If you look in column Ad, AE and AF you will see the formula that we are having an issue with. If you open this workbook and check the values in these columns and the open workbook in Excel2011 the data in the columns are different. The data in the "Windows" version is correct.( I had to do a replaceall for the "=" for it to calculate in Windows for some reason the Auto and F key did not do the cal)
    They should for column AD have a value range of 3.00 to 5.00
    Column AE value range 5.00 to 11.00 and for column AF about the same as AE.
    Any help would be appreciated.
    Attached Files Attached Files
    My Time is valuable. The forum's time is valuable.
    So take time to say "Thanks"

    As with all programs there may be an easier way!
    CharlesH

  2. #2
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    Excel 2004 appears to calculate correctly, Excel 2011 does not. Problem seems to be that 2011 countif/sumif/averageif do not like having ranges across sheets. When I put the par/score data on the same sheet, averageif works.

    try using an array formula, for example:

    =IF(ISNUMBER(Score!U3),AVERAGE(IF(Par!C3:U3=3,Score!C3:U3)),"")
    instead of hitting return, press command-enter on the mac (ctrl shift enter on PC) to commit the formula; you'll see the curly braces added when you do this.

    I did not test extensively, but it seems to work in 2011 and should also work in windows the same. you should be able to adapt to your other columns to suit. good luck!
    Last edited by Aussiebear; 04-11-2023 at 04:00 PM. Reason: Adjusted the code tags

  3. #3
    VBAX Regular
    Joined
    Oct 2010
    Posts
    73
    Location
    tpoynton,

    HI was gone for the past couple of days. Thanks for your suggestion. I sent it to my friend and am waiting on his reply. I tested you suggestion and I receive the see the same value in the Mac and PC. Will wait until I hear from him before I colse the thread. Thanks again.
    My Time is valuable. The forum's time is valuable.
    So take time to say "Thanks"

    As with all programs there may be an easier way!
    CharlesH

  4. #4
    VBAX Regular
    Joined
    Oct 2010
    Posts
    73
    Location
    tpoynton,

    Thanks for the reply and as of today your suggestion seems to be working.
    My Time is valuable. The forum's time is valuable.
    So take time to say "Thanks"

    As with all programs there may be an easier way!
    CharlesH

  5. #5
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    I probably learned more than anyone else did, so thank you!

Posting Permissions

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