Replace Those Megaformulas
In the Sample Code section there is a
function which extracts a numerically formatted date from a text string of any
length. It’s a fine example of functionality not already in Excel, but custom
functions can also be used to ‘package’ existing Excel functionality. You can use VBA functions to replace MEGAFORMULAS.
Can you figure out what
the following formula does? Below is the spreadsheet using it.
=IF(B2="CA",ROUND(C2*0.0825,2),IF(B2="NV",ROUND(C2*0.0735,2),0))
It calculates the sales tax rounded to two decimal places on a purchase made
in any of three western states. Three states? Well, the individual who wrote the
formula only sells in CA, NV, and OR. Since Oregon has no state sales tax, they
just left a zero in the final IF clause. That’s a problem when they start
selling in Washington or any other state.
If you look at the spreadsheet, you might notice another little problem. Why
does the purchase in Truckee show no sales tax and the one in Bend show sales
tax? If you’d like to try to find out, download the
file.
The formula in this example isn’t even a megaformula, it’s more like a
kiloformula and we already have problems creeping up. This is the essential
problem with megaformulas. They’re great when they work, but they’re just
horrible when they go wrong.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
You can use VBA to create a custom function for your spreadsheet. The
function below can be used in the spreadsheet by simply typing
=Tax(B2, C2) into cell D2. Anyone looking at the
cell will know that sales tax is calculated in the cell, and when rates change
or new states and counties are added, the custom function can be updated in one
place rather than in a series of cells.
For more on using VBA, ask about our
custom training
classes.
Function Tax(strState As String, PPrice As Double)
As Double
'I Get It! Development does not actually know what
sales tax is
'from state to state and county to county. Do not use these
'figures for any actual calculations!
Select Case strState
Case Is = "CA"
Tax = PPrice * 0.0825
Case Is = "NV"
Tax = PPrice * 0.0735
Case Is = "OR"
Tax = 0
Case Else
Tax = PPrice * 100 'This will make unmatched states STAND OUT
End Select
End Function
|
|