The ongoing prattlings of a lifelong geek and his random luck with love, work, children and rediscovering himself.

2010-09-24

Excel 2007 Is Broken - Not

It's amazing how important placement is in a formula.

    =IF( MAX( RAW!$Q:$Q > 365 ) , 365 , MAX( RAW!$Q:$Q ) )

The above formula is perfectly fine.  If it's what you want.  What you probably wanted was:

    =IF( MAX( RAW!$Q:$Q ) > 365 , 365 , MAX( RAW!$Q:$Q ) )

Sadly, this is one of those quirks (in my case at least) where the proper value is returned if the formula exists on any row EXCEPT ROW A.  I had three people looking at this formula (first one above) and none of us figured it out... until I typed the formula from scratch and asked myself "Why is my evaluation INSIDE the MAX?".

Duh.  Fun fun.  Not.  I was so proud that I though I'd found an actual fault in Excel, too...  (and for those curious, the data being checked sometimes has less than a year of rows -- I need to show one year, or less if needed)

No comments:

Post a Comment