# How To Predict Bearing Life With Excel

When you work in mechanical engineering, understanding the reliability and performance of bearings under various conditions is crucial. Bearings are the cornerstone of many mechanical systems, and their failure can lead to catastrophic results.

This is where calculating bearing life, specifically the L10 life, becomes an invaluable skill. The L10 life represents the lifespan at which 90% of a group of bearings will still be operational under a given load and conditions.

## Bearing Life Essentials

### What is L10 Life?

The L10 life calculation is a statistical approach to predict the life expectancy of ball bearings and roller bearings under certain loads and operating conditions.

It’s a crucial figure that helps engineers design more reliable and efficient machines. This calculation estimates the number of rotations at which 90% of the bearings are expected to still be in service.

## Leveraging Excel for Bearing Life Calculations

Excel offers powerful tools to automate and simplify the process of calculating bearing life. However, it requires a bit more than just plugging numbers into formulas. You’ll need to perform data lookups and, more interestingly, bilinear interpolation to accurately interpolate parameters based on various operating conditions.

### Bilinear Interpolation in Excel

Bilinear interpolation is a method of mathematically finding a point’s value within a square formed by four other points. It’s not a native Excel function, but with some creativity, you can implement it through a user-defined function.

For bearing life calculations, bilinear interpolation allows you to accurately determine factors like the rated viscosity and contamination factor based on the bearing’s operating conditions.

=BiLinInterp(x,y,xvalues,yvalues,zvalues)

### Accessing SKF Bearing Data

One of the largest manufacturers of bearings, SKF, provides extensive data and reference materials for their products. This data includes curves and charts that are essential for calculating bearing life, such as the ηc factor for different lubrication conditions.

You can digitize these curves and then use them within Excel to perform your calculations more accurately.

### Implementing Excel Functions for Bearing Calculations

Let’s explore how to use some of Excel’s functions in the context of bearing life calculation.

VLOOKUP/XLOOKUP: These functions are essential for pulling specific data related to bearing dimensions, load ratings, and more from extensive tables.

Example Formulas:

=VLOOKUP(value, table, col_index_num, [range_lookup])

=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)

INDEX and MATCH: These functions work together to replace VLOOKUP/XLOOKUP in more complex scenarios, such as when interpolating values or when data is not organized in a single column or row. They are particularly useful in bilinear interpolation.

Example Formula:

=INDEX(range, MATCH(lookup_value, lookup_array, [match_type])

## Practical Application: Calculating Bearing Life

Consider a scenario where you need to calculate the L10 life of a specific bearing operating under a radial load, with a known operating speed and fluid cleanliness level. You will use the SKF provided data to determine the bearing’s rated viscosity and then calculate the L10 life using the provided formulas.

1. Calculate the Mean Diameter (dm): This is the first step and involves simple arithmetic based on the bearing’s inner and outer diameters.

2. Determine the Rated Viscosity (v1): Using bilinear interpolation with the digitized SKF data, you calculate V1 based on the mean diameter and operating speed.

3. Calculate the Lubrication Condition (K): This is the ratio of the actual operating viscosity to the rated viscosity (v1). It’s a straightforward calculation but crucial for understanding the bearing’s lubrication status.

4. Find the Contamination Factor (ηc): Again, using bilinear interpolation on SKF’s contamination data, you determine the impact of fluid cleanliness on bearing life.

5. Calculate the L10 Life: Finally, using the calculated values, you apply the L10 life formula. This gives you the expected lifespan of the bearing under specified conditions.

By following these steps and utilizing Excel’s powerful features, you can accurately predict the bearing life, ensuring more reliable and efficient mechanical designs.

This blog post is based on a new engineering spreadsheet that’s available inside the EngineerExcel Academy. Members can access this spreadsheet for free, offering you an incredible opportunity to enhance your Excel skills for engineering applications.

Scroll to Top
Complete... 50%