Excel Tips Regarding Formulas and Breaks

Recently when creating an overtime pay adjustment calculator, I ran into some tricky issues in regards to the calculator to not show values when certain cells in the formula are blank.  I did this as to not confuse the end-user with numbers that are not correct without all the appropriate values filled in.

As you can see in the above calculator, they "System Generated" values are dependant upon user required values as well as system caclulated values.  Done normally, some fields would populate with values without all the required inputs completed.

My first try was to use the ISBLANK function.  So in cell C25, the formula was =IF(ISBLANK(C17),"",C17+C12) and this work correctly.  However, I was receiving an error in C27 with the formula IF(ISBLANK(C25),"",(C25/40)*1.5) and the only reason I could figure was due to C25 also having a ISBLANK function.  However, if I changed them both to use the LEN function, everything worked fine.  So the final formulas in each cell are as follows:

C25 =IF(LEN(C17),C17+C12,"")
C27 =IF(LEN(C25),(C25/40)*1.5,"")

More tips to come as I come across them.  Have fun!

Excel Hints is Giving Away Over $1400 in Prizes

My favorite software to use happens to be the one I use all day, every day, at work.  I am one of the weird people whom enjoy Excel.

An earlier Twitter I gave a link to my RSS Feeds for Excel blogs and the like.  One of those blogs that I read almost everyday is Excel Hints.  Currently, Excel Hints has a give-away contest going and if you are someone that would like to win Excel consulting or Office 2007, along with a couple other prizes, just go here and subscribe to their Excel Hints email.

Good luck!