Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

problem with an array formula

Latest post Thu, Feb 26 2015 8:13 PM by Garry. 4 replies.
  • Wed, Feb 25 2015 3:47 PM

    • Garry
    • Top 500 Contributor
    • Joined on Wed, Feb 25 2015
    • Posts 3
    • Points 47

    problem with an array formula

    First of all this is for calculating overtime that is set up so that every 3 minutes is .05 of an hour. I have a table set up where F3:F62 represents 1.00 to 60.00 minutes and G3:G62 represents .05 to 1.00. B9 is 12/24/2014 12:00 AM and B10 is 12/23/2014 11:55 PM.                                                                                                                           This is my array formula {=SUM(IF($F$3:$F$62=TEXT(B9-B10,"m.ss"),$G$3:$G$62,0))} my answer should be .10 (5.00 minutes) of an hour but i get nothing. What am I doing wrong?

     

    • Post Points: 21
  • Wed, Feb 25 2015 7:45 PM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Re: problem with an array formula

    You are comparing text to numbers. Try this fix:

    =SUM(IF($F$3:$F$62=TEXT(B9-B10,"m.ss")*1,$G$3:$G$62,0))

    Your example can also be dealt with using a vlookup formula which is easier for normal people to understand:

    =VLOOKUP(TEXT(B9-B10,"m.ss")*1,F3:G62,2,FALSE)

    If you do need to do math rather than a lookup, try this one instead of your array formula:

    =SUMIFS($G$3:$G$62,$F$3:$F$62,TEXT(B9-B10,"m.ss")*1)

    Omar Freeman Kitchener, ON

    • Post Points: 21
  • Wed, Feb 25 2015 9:40 PM In reply to

    • Garry
    • Top 500 Contributor
    • Joined on Wed, Feb 25 2015
    • Posts 3
    • Points 47

    Re: problem with an array formula

    That you very much. I just don't understand what multiplying by 1 is doing.

    • Post Points: 21
  • Thu, Feb 26 2015 8:06 AM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Re: problem with an array formula

    Excel decides that doing math on a number supercedes the text function you had just done on the code. Changing your elapsed time to a number allows the comparison to the numbers in your column to actually work. Remember that text and numbers are not the same, even though they look the same to us. I've been bit by this many times because my database queries often bring information that looks like numbers into tables, but they are really being treated as text by Excel.

    Omar Freeman Kitchener, ON

    • Post Points: 21
  • Thu, Feb 26 2015 8:13 PM In reply to

    • Garry
    • Top 500 Contributor
    • Joined on Wed, Feb 25 2015
    • Posts 3
    • Points 47

    Re: problem with an array formula

    Thank's again. I remember having to use this once before but didn't understand why so your explanation is very helpful. Thank's again Garry......

    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.