As a person data analyst working in the retail industry, imagine you’re tasked with simulating weekly retail sales that could represent the variability in daily sales with higher values during weekends (multiples of 7) and lower values during weekdays? Or you’re working as a data analyst in the energy firm and your manager asked you to simulate weekly energy consumption patterns, accounting for increased demand during weekends and reduced demand during weekdays? Or you’re working as an HR analysts and HR Director tasked you to simulate weekly fluctuations in employee output, with different performance expectations during weekdays and weekends? In this article, we are going to write an Excel formula to generate dataset that mirrors the above scenarios.
In a cell, execute the formula below:
=IF(MOD(ROW(),7),RANDBETWEEN(9500,50000),RANDBETWEEN(45000,100000))
Click Enter and copy down the formula using the fill handle.
Understanding the Excel Formula
The ROW(): returns the current row number, laying the groundwork for weekly pattern.
MOD(ROW(), 7): calculates the remainder when dividing the row number by 7, establishing a clear weekly cycle.
IF(MOD(ROW(), 7), … , …): evaluates the formula based on the weekly pattern. Succinctly, it implies that if the row is not a multiple of 7, one set of parameters is applied; otherwise, a different set is utilized.
RANDBETWEEN(9500,50000): generates a random number between 95,000 and 50,000 for non-multiple-of-7 rows.
RANDBETWEEN(45000,100000): generates a random number between 45,000 and 100,000 for rows that are multiples of 7.
The formula is much better than the traditional ways of performing randomization. See you in the next totorial.