Daily posts of Excel tips…and other stuff
Archive for the ‘Worksheet Functions’ Category.
Tom asks:
I have three values with wind direction at differrent altitudes.
A1: 020, A2: 2000 feet,
B1: 010, B2: 5000 feet,
C1: 350, C2: 10000 feet
and I would like to forecast the wind direction for D2: 3000 feet. I’ve tried the forecast function but it goes haywire with the change from 010 to 350 degrees.
I made [...]
Here are three ways to lookup a value based on two columns. Let’s start with this random data:
And let’s say that we want to lookup the autonumber value for Carlo. Not the Carlo in Seaford, but the Carlo in Westwood.
SUMPRODUCT
With ‘Carlo’ in E2 and ‘Westwood’ in F2, use =SUMPRODUCT(($A$2:$A$16=E2)*($B$2:$B$16=F2)*(C2:C16))
If you have more [...]
As you know, the fourth argument of the VLOOKUP worksheet function determines whether VLOOKUP finds an exact match or an approximate match. If you set the last argument to False, and there is no exact match in the lookup list, the formula will return #N/A!.
In Excel 2003, the best way to capture this error, [...]
I have a range that should only contain numbers. Blanks are OK, but I need to determine if anyone "deleted" a cell by typing a space or entered text in any other way. This worksheet formula returns TRUE if there is text in the range:
=COUNTA(A1:C6)-COUNT(A1:C6)>0
COUNTA counts all the non-blank cells while COUNT counts [...]
It's college football bowl season once again. I have a spreadsheet I use to help me identify winners and losers (for entertainment purposes only).
The four yardage columns are yards per game for rushing offense, rushing defense, passing offense, and passing defense, respectively. I'm trying to identify teams that have better defenses (because defense [...]
I'm working on some VBA to take some of the drudgery out of payroll.
Public Function ThisFriday() As Date
ThisFriday = Date + 8 - Weekday(Date, vbFriday)
End Function
Public Function LastFriday() As Date
LastFriday = Date + 1 - Weekday(Date, vbFriday)
End Function
I thought I needed to know [...]
I have to save a file that may have the same name as an existing file. If it does, I append a number to the end of it to make it unique. The problem is that the file will live in three different folders in its life; Working, Review, and Archive. I [...]
Excel provides an easy way to create a table of hyperlinks and even save the workbook as a web page. The built-in method for creating a hyperlink is Insert > Hyperlink (Ctrl+K). The problem with this method is mass changes, like renaming your server. Editing each one (if you have a lot) [...]
