≡ Menu

Function

Working Day Formulas for Monthly Finance Timetables

Working Day Formulas for Monthly Finance Timetables Thumbnail

I've just inherited a monthly finance timetable that provides a series of working day deadlines for each month of the year. The way it has been set up requires quite a bit of manual adjustment to ensure that bank holidays and weekends are reflected. In trying to neaten this up a little bit, I've been [...]

{ 0 comments }

Excel’s Missing RANKIF Function

Excel’s Missing RANKIF Function Thumbnail

Excel offers a number of conditional IF functions such as COUNTIF and SUMIF which allow you to count or sum a range based on the values in another column. If you have Excel 2007 or later you’ll have the really useful COUNTIFS and SUMIFS functions which allow you to perform multi-condition actions based on the [...]

{ 3 comments }

Sumproduct Function and Conditional Criteria

Sumproduct Function and Conditional Criteria Thumbnail

The sumproduct function is one of the most useful and powerful features of excel but the online help available from microsoft is unfortunately pretty poor. It works as an advanced form of the SUMIF formula where you can sum a range of values based on a number of different criteria. Here is the simple formula [...]

{ 0 comments }

The SUMIF Function using Named Ranges

The SUMIF Function using Named Ranges Thumbnail

Named ranges are useful in Excel as their use can make formulas much more understandable. You can name: areas of the spreadsheet, such as your data columns and rows or individual cells that may act as constants, for example the VAT value I’ve used the following named ranges in the SUMIF example below: As usual [...]

{ 1 comment }

How to Test for an Integer Value in Excel

How to Test for an Integer Value in Excel Thumbnail

I was looking around today for an ISINTEGER logic function in Excel but I can save you the bother and tell you it doesn’t exist. I picked up fractional activity counts in our reported YTD position and wanted to quickly highlight which lines were affected so that I could assess the problem. You may expect [...]

{ 1 comment }