Daily posts of Excel tips…and other stuff
Archive for the ‘Formulas’ Category.
Let’s say you have some data like, oh I don’t know, First Name, Last Name, Gender, State, and some number.
You could add up the numbers for all the males in Texas with an array formula.
=SUM((C2:C101=”Male”)*(D2:D101=”Tx”)*(E2:E101))
If you didn’t know that, go read Anatomy of an Array Formula. One of my workshop attendees had [...]
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 [...]
Ron sez:
What happens often is that one person will have one of these spreadsheets open and the other spreadsheets linked to the subject spreadsheet closed and decide to insert a column that throws off the references of the closed linked spreadsheets. If the person knew which spreadsheets were linked to it, the person could open [...]
I recieved this email from my pal Simon:
“One of the problems in many of the spreadsheets and potential spreadsheets I come across is the need to automatically adjust to changing areas of external data. It seems at first glance that, to refer to a single column in a block of external data, the intersection operator [...]
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) [...]
I read my retirement account statement today. It wasn't pretty. A lot of low, single digit returns lately. I know people who complain when the market tanks, but I'm not one of them. When it comes to my retirement, I want the market to keep going down until I'm 59 1/2, [...]
Hi,
A while ago I requested beta testers for a new utility, now called "ExcelRefTool".
Thanks to my beta testers, the tool is now mature enough to be exposed to the general public.
Have a look here, download the demo if you like and give it a spin.
And thanks again, to everyone who took the trouble of beta [...]
I wrote a VBA user-defined function for this task, but I can't help but think there's a worksheet formula that can do the job. In case you were looking for something to do this weekend, here's something to occupy your time.
You get a two column range, the starting group, the ending group, the number [...]
