Excel - Count Your Eggs - Where 10-15 Means 10 Trays of 30 and 15 - Excel - Episode 1726

1,841 views · Published 7 June 2013 · 9:52 · Indexed 23 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Summing Eggs When Data Shows Full Trays and Loose Eggs?

Welcome back to another episode of Dueling Excel, where we take on challenging Excel problems and come up with different solutions. In this episode, we tackle a question sent in by a viewer who is trying to add up eggs using alternate number systems. The challenge is to add up the number of trays and eggs, and then reduce it to a simplified form. For example, 10-15 would mean 10 trays and 15 eggs, with each tray containing 30 eggs. We need to find a way to add these numbers and then reduce it to a form like 2 trays and 5 extra eggs.

The only way I know to solve this problem is by using VBA and writing a custom function. So, let's switch over to VBA and take a look at the SumEggs function that I have created. This function uses the SUM function in Excel and is specifically designed to add up the total number of eggs. We use a shortcut to define WF as Application.WorksheetFunction, so we don't have to type it out every time. The function then loops through each cell in the range and uses the Find function to locate the dash. It then takes the number of trays and multiplies it by 30, and adds the number of loose eggs to get the total number of eggs. This is a single line of code that adds up all the eggs and ignores the trays. After processing all the cells, the function returns a value back to Excel in the form of FullTray-LeftoverEggs.

But wait, there's more! My co-host, Mike Girvin, has come up with an even more complex formula to solve this problem. He uses a combination of functions like LEFT, SEARCH, REPLACE, and QUOTIENT to add up the trays and eggs. The formula is quite long and involves multiple steps, but it gets the job done. Mike's formula is a thing of beauty and makes me question the need for VBA. He also shares a clever trick of copying all the bits to the clipboard and using the replace function to make the formula more efficient. It's amazing what we humans can make Excel do!

In conclusion, this was definitely one of the harder problems we have tackled on Dueling Excel. But with the help of VBA and some clever formula tricks, we were able to come up with solutions to add up eggs using alternate number systems. We hope you found this episode helpful and learned something new. Don't forget to like, share, and subscribe to our channel for more Excel tips and tricks. And as always, keep on Excelling!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Adding up eggs in trays and loose eggs
(00:19) Using VBA to create a custom function
(00:40) Writing the SumEggs function in VBA
(01:52) Calculating the total number of eggs
(02:08) Returning the value back to Excel
(02:26) Testing the function with different values
(03:10) Introduction to Mike's solution
(03:20) Mike's formula using LEFT, SEARCH, and SUMPRODUCT
(05:24) Calculating the total number of trays from the LEFT
(06:02) Calculating the total number of eggs from the RIGHT
(07:06) Using QUOTIENT and MOD to get the integer and remainder
(08:33) Copying the formula to other cells using REPLACE
(09:35) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding up eggs
Alternate number systems
Dueling Excel podcast
Find function
FullTray calculation
Left and Mid functions
Left, Search, Replace functions
Looping through cells
SumEggs function
Total trays and total eggs calculation
User-defined function
VBA custom function



Dueling Excel Podcast #122...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] http://www.mrexcel.com/slayingdragonsebook2011.html 
            AND
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved!
35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE2010.html 

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/dueling-excel-alternate-number-systems-podcast-1726.1153548/

 About MrExcel: Bill Jelen is an Excel MVP and the author of 70+ books on Excel. He has trained thousands of professionals worldwide.

More from this channel