by Ilker | Apr 4, 2018 | Excel Tips & Tricks
Sure, it’s easy to find the highest or smallest values in a data set. But what if you’re looking for the 3rd, 10th, 20th rank in a large table, or need to do this in ascending and descending order? Luckily, Excel features a formula that does just that....
by Ilker | Apr 2, 2018 | Excel Tips & Tricks
The IF function is great for creating a logic statement. But IF is an either/or argument, what if you need more alternatives? Combining several IF statements in a nested structure can help overcome this. Syntax =IF(first logical test, do something if first test is...
by Ilker | Apr 2, 2018 | Excel Tips & Tricks
A final exam is worth more in terms of graduation score and the term weighed average becomes relevant. In such cases, SUMPRODUCT formula can help calculate the weighed totals of a data set. In our example, test scores of 3 candidates are being compared. Although...
by Ilker | Apr 2, 2018 | Excel Tips & Tricks
A quick and easy way to count between dates is by using the COUNTIFS formula. Create formula-based filters, find your sales figures, or organize your tables in no time. Syntax =COUNTIFS(date range, >=minimum date, date range, <=minimum range) Steps Type...
by Ilker | Mar 29, 2018 | Excel Tips & Tricks
How much did you spend until last year, or what is the total of X until Y? SUMIF and SUMIFS functions can help you to sum values in your data tables. Syntax =SUMIFS(values to sum range, date range, <maximum date) =SUMIF(date range, <maximum date, values to sum...
by Ilker | Mar 29, 2018 | Excel Tips & Tricks
Using the versatile SUMIF function, see how you can create cell totals from a certain date. Creating a YTD (year-to-date) reports has never been easier. Syntax =SUMIFS(values to sum range, date range, >minimum date) =SUMIF(date range, >minimum date, values to...
by Ilker | Mar 29, 2018 | Excel Tips & Tricks
Be it for budgeting, reporting, or analysis, you can easily sum data within a date range using the SUMIFS formula. SUMIFS is an extended form of SUMIF formula where you can enter multiple criteria. Syntax =SUMIFS(values to sum range, date range, >=minimum date,...
by Ilker | Mar 28, 2018 | Excel Tips & Tricks
Do you have a data table where you need to pick all the highest, lowest, best, worst, or equal to 2? SUMIF, and its bigger brother SUMIFS formulas can help sum cells of your choice. Syntax =SUMIF(criteria range, criteria, values to sum range) =SUMIFS(values to sum...
by Ilker | Mar 27, 2018 | Excel Tips & Tricks
Using the SUM formula this way will allow your data tables to grow and include any new items by running totals. Syntax =SUM(Absolute reference, Relative reference) Steps Type SUM function with a range that contains only single cell =SUM(C3:C3) Make range’s first cell...
