How to Generate Random Contact Numbers & Dates Using RANDBETWEEN Function

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to use one of the sophisticated functions in Excel named RANDBETWEEN RANDBETWEEN returns a random number between numbers specified. The function is elegant to generate random date. RANDBETWEEN Syntax                  =RANDBETWEEN(bottom, top) We want to generate random contact number and dates in columns B and C […]

Continue reading

LARGE & SMALL Array Functions: Return Top 8 and Bottom 8 Values

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to use LARGE and SMALL functions to display Top 8 and Bottom 8 values LARGE Function returns the k-th largest value in a data set LARGE Syntax                    =LARGE(array, k) SMALL Function returns the k-th smallest value in a data set SMALL Syntax                    =SMALL(array, k) In […]

Continue reading

Power Query Mash-up Function: Unpivot Columns using Table.Unpivot

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to unpivot columns in Power Query using mash-up function Table.Unpivot M Function translates a set of columns in a table into attribute-value pairs combined with the rest of the values in each row Table.Unpivot Syntax                     =Table.Unpivot( table, pivotColumns, attributeColumn, valueColumn) We want to unpivot the […]

Continue reading

R1C1 Referencing Style: Switch Column Letters To Numbers Using ALT + FTF Shortcut

Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to switch spreadsheet columns alphabet letters to numbers using R1C1 referencing. Let’s get started Open an Excel workbook and execute the command: ALT + FTF In the Excel Options dialogue box, check the R1C1 reference style Click OK In the caption above, we can see […]

Continue reading

LARGE & AVERAGEIFS Functions With Logical Operators

Welcome to Excel Jet Consult blog post. In this post, we will learn how to combine LARGE & AVERAGEIFS Functions with Logical Operators. Let’s get started LARGE Function returns the k-th largest value in a data set LARGE Syntax                    =LARGE(array, k) AVERAGEIFS Function returns the average for the cells specified by a given set of conditions or criteria AVERAGEIFS Syntax        […]

Continue reading

Using SUM & CHOOSE Functions To Calculate Total Sales

Welcome to Excel Jet Consult blog post. In this post we want to learn how to use the rarely known lookup family function – CHOOSE. We will wrap the CHOOSE formula inside sum function. CHOOSE Function chooses a value to perform from a list of values based on index number CHOOSE Syntax                =CHOOSE(index_num, value1, [values2], …) In the caption below, […]

Continue reading