Excel Formula for Lotería

Overview

Lotería is a traditional Mexican game similar to bingo, where players have a board displaying images instead of numbers. Players seek to match called images on their boards. In Excel, we can replicate aspects of Lotería, such as randomly drawing images and counting matched cards.

Objective

This formula aims to:

1. Randomly draw an image from a list of images.
2. Check if that image is on the player's board.
3. Count the number of matches over multiple draws.

Formula Components

1. List of Images: Assume you have your images listed in cells A1:A16.
2. Player’s Board: Let’s assume the player's selected images are in the range B1:B16.
3. Random Draw: Use INDEX and RANDBETWEEN to randomly select an image.
4. Count Matches: Use COUNTIF to check for occurrences of the randomly drawn image on the player's board.

Formulas

Randomly Draw an Image

To randomly select an image from the list:

=INDEX(A1:A16, RANDBETWEEN(1, COUNTA(A1:A16)))

Check for Matches on Player's Board

To count how many times the drawn image appears on the player's board:

=COUNTIF(B1:B16, INDEX(A1:A16, RANDBETWEEN(1, COUNTA(A1:A16))))

Formula Explanation

Random Draw Formula

• INDEX(A1:A16, RANDBETWEEN(1, COUNTA(A1:A16)))
  • INDEX retrieves the value from the specified range A1:A16.
  • RANDBETWEEN(1, COUNTA(A1:A16)) generates a random integer between 1 and the number of entries in A1:A16. This ensures each image has a chance to be drawn.

Count Matches Formula

• COUNTIF(B1:B16, INDEX(A1:A16, RANDBETWEEN(1, COUNTA(A1:A16))))
  • COUNTIF(B1:B16, ...) counts how many times the randomly drawn image appears in the player’s board range.
  • The inner INDEX function serves the same purpose as in the random draw formula, seamlessly pulling the drawn image for the count comparison.

Practical Example

Setup

1. List of Images (A1:A16):

   • Cactus
   • Sun
   • Moon
   • Heart
   • Star
   • etc.

2. Player's Board (B1:B16):

   • Sun
   • Cactus
   • Fish
   • etc.

Execution

1. Place the first formula in cell C1 to randomly draw an image.
2. Place the second formula in cell C2 to count matches.

Results Interpretation

• C1 will show the randomly selected image.
• C2 will display how many times that image exists on the player's board.

Conclusion

This approach allows you to simulate a Lotería game environment in Excel, enabling random draws and match counts. Adjust the ranges as necessary for different board sizes or rules. For more advanced usage and data manipulation techniques, consider exploring courses on the Enterprise DNA Platform.