[Porting] How to Replace Excel’s RAND and RANK Functions with Python | Implementing with pandas and numpy

Overview

I have previously used Excel functions like =RAND() and =RANK() to generate random values and process rankings. However, as I move towards improving business efficiency and automating processes, I wanted to reproduce similar processing using Python.

In this article, I implemented the processing of Excel’s RAND() and RANK() functions using Python.

We will be using the numpy and pandas libraries.

Desired Specifications

1. Assign a random value to each name in a list (equivalent to Excel’s RAND()).
2. Calculate a ranking based on the assigned values (equivalent to Excel’s RANK()).
3. Save the results to a new Excel file.

Implementation Code (Python)

import numpy as np
import pandas as pd

# List of names (5 people as an example)
names = ['Alice', 'Bob', 'Charlie', 'David', 'Eve']

# Generate random values (Uniform random numbers between 0.0 and 1.0)
# Equivalent to Excel's =RAND()
random_values = np.random.rand(len(names))

# Create a DataFrame
df = pd.DataFrame({
    'Name': names,
    'Value': random_values
})

# Rank based on the 'Value' column (Higher value = Rank 1)
# Equivalent to Excel's =RANK()
df['Rank'] = df['Value'].rank(ascending=False)

# Output results to Excel
df.to_excel('ranked_names.xlsx', index=False)

By using this code, the following processing is achieved:

• np.random.rand() generates random values just like the RAND() function in Excel.
• df['Value'].rank() executes processing similar to the RANK() function.
• The ranking is set to descending order (ascending=False), meaning larger values get higher ranks.
• The final result is saved as ranked_names.xlsx.

Output Result Example

The generated Excel file will look like this:

Note: Since these are random values, the results will differ every time you run the code.

Summary

• Excel’s RAND() can be replaced with numpy.random.rand().
• Excel’s RANK() can be reproduced with pandas.DataFrame.rank().
• Processing with Python allows for high-speed handling even with large amounts of data.
• By outputting the results in Excel format, integration with existing Excel workflows remains smooth.

For those looking to port Excel tasks to Python or considering business automation, I believe this is basic yet highly practical content.

Thank you for reading to the end. I hope this helps.