﻿ problem with an array formula - Excel User Group Excel User Group Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

problem with an array formula

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

• Garry
• • • 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
• • • 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
• • • 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
• • • 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
• • • Joined on Wed, Feb 25 2015
• Posts 3
• Points 47