Resources
How to get the decimal part of numbers in a data set
Identifying the detail level of values in a data set can be useful for analysis of results. This simple trick can help you separate the decimal values from the numbers for evaluation. Syntax =number - TRUNC(number) (only for positive numbers) =number -...
How to shorten a number and get the integer value
Do you need to shorten number, but not round them? Need to find the nearest INTeger or TRUNCate numbers in Excel? Yep, Excel has two functions that do exactly that! You can shorten numbers using the TRUNC and INT functions while preserving the original...
Human Resources Dashboard
This human resources dashboard template in Excel visualizes various employee and HR related measures in an interactive manner. It allows human resources managers and senior management quickly view employee statuses including payroll, sick days, employee performances...
How to rank data in Excel tables
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....
How to use multiple IF functions
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...
How to calculate weighted average with SUMPRODUCT
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....
How to COUNT values between two dates
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...
How to SUM values if date is less than
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,...
How to SUM values if date is greater than
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,...