Friday, November 27, 2009

8 levels or more IF function for EXCEL 2003 or below

"the specified formula cannot be entered because it uses more levels than are allowed in the current file format"
For those who actively use Microsoft Excel, may had found the error statement above. It caused by the limitation of nesting level of older version of the application (2003 and below). They only support up to 7 nesting level of a function in one statement.

There are some alternative ways to get around, here's some of them:
  1. Upgrade your EXCEL to 2005 or above.
  2. If you had a macro programming skill, create a function in VBA.
  3. or, you can split the function up to some parts.
Here, we'll try the last option, Split up the function.

I have this function that had the nesting level issue:
=IF(H6<91.5%,40%,if(h6<92.5%,35%,if(h6<93.5%,30%,if(h6<94.5%,25%,if(h6<95.5,20%,if(h6<96.5%,16%,if(h6<97.5%,12%,if(h6<98.5%,8%,if(H6<99.5%,4%,0%)))))))))

as the limitation goes, than the function will only work if we cut the red part off:
...if(H6<99.5%,4%,0%)...
But that won't solve the problem. So, I came up with this way.
  • Take the 8th level of nesting out (let's say use the C2 cell) and exchange it with "FALSE" value, so the function will look like this:

=IF(H6<91.5%,40%,if(h6<92.5%,35%,if(h6<93.5%,30%,if(h6<94.5%,25%,if(h6<95.5,20%,if(h6<96.5%,16%,if(h6<97.5%,12%,if(h6<98.5%,8%,FALSE))))))))
  • Put the 8th IF to another cell and put another IF statement before it to check the output value of the IF statement above (from the C2 cell). If it return FALSE then execute the 8th IF. So the second part of the function will be like this:
=IF($C2=FALSE,IF(H6<99.5%,4%,0%))

  • The last part is to choose which output to use. Here we'll use another IF. Check whether the return value of the first statement is FALSE or not. If it is, then use the return value of the second if statement. If not, then just use the value. Here's what it would be like:
=IF($C2=FALSE,$D2,$C2)
There you go! We finally made it. Hope you'll find this article useful. If you want to see the real example of the EXCEL format, I have uploaded it here. Have a good day!

Thursday, November 12, 2009

Windows Vista Hibernate Feature

I recently upgraded my OS to Vista (as one can see on one of my previous post). The elegant look-and-feel and better security -or so they claimed- had made me to take this journey (har..har...har...). I know but a bit about the Security issues, but the look-and-feel is one thing for certain. If you talked about ease-of-use, one can say that it's about fifty-fifty, like I found it easy to connect to a network but it's a walk-in-a-maze to find out how to tweak the setting.

I'm mobile and I need my laptop to boot-up and shut-down faster. If I were to follow the normal sequence of powering-up and shutting-down, it'll take forever. So I've found this HIBERNATE feature on XP litterally cut-the-middle-man. It could cut up until half of the time I'd needed on those routines.

But...! This won't happened on my Vista...!!!

Like I said earlier, I really went on a maze with Vista trying to find the setting I need, but came up with nothing but frustration. So I turned to my beloved uncle google and found out that Vista is no longer giving it's users the luxury to enable or disable hibernation via desktop graphical user interface.

So here's how to re-enable it:
  1. Type cmd into your Vista's Start Menu Search Box, and use Ctrl+Shift+Enter or you can also right-click the item in the menu and choose Run as Administrator to open in administrator mode.
  2. Type in the command powercfg -h on in the appearing command prompt window.
  3. You're Done...! Here's the screen shots of before-and-after the process.
Before

After

And if you ever want to turn it off again, well... I think you can guess what to type in. (^_^)

Note: Here's what Microsoft has to say