Yesterday was 14 February, 2020. Valentine's Day. I was thinking about to share an interesting think on this special day. Finally I decided to make the very popular Love Calculator in Microsoft Excel. Sounds interesting. Yea. The process to make Love Calculator in Excel has described below:
This is very easy to create it. But you need to concentrate on it. Here I've used very popular INDEX-MATCH Function and no VBA. So, let's going start.
Step 1:
Image 2: Valentine's Day Love Calculator Fun Game Calculation
Step 2:
The D2 cell contains a name, here i used "Microsoft". It needs to convert into Numbers through A1:B6 data table. You can change as you like in A1:B6 data table. So, I have used below formula to convert the "Microsoft" into number as per character matching.
In D4 Cell, enter below formula and Copy it down to D23 cell.
=IFNA(INDEX($B$2:$B$6,MATCH(MID($D$2,ROWS($D$2:$D2),1),$A$2:$A$6,0)),0)
Step 3:
In the same way use the below formula in G4 cell and Copy it down to G23 cell:
=IFNA(INDEX($B$2:$B$6,MATCH(MID($G$2,ROWS($G$2:$G2),1),$A$2:$A$6,0)),0)
Step 4:
In E2 cell use the =SUM(D4:D23) formula to summarize the total number of "Microsoft" name.
Step 5:
In H2 cell use the =SUM(G4:G23) formula to summarize the total number of "Excel" name.
Step 6:
In J2 cell use the =IF((SUM(E2,H2)*1%)>=1,99%,SUM(E2,H2)*1%) formula to convert these 2 numbers (E2 and H2) into percentage (%).
Step 7:
Now take a New Worksheet and plot like below with AutoShape, Font and Text:
Image 3: Valentine's Day Love Calculator in Excel - Game desig
Step 8 :
Marge this B13:D14 cell so that, user can enter name in B13 cell. In the same way Marge the J13:L14 cell.
Step 9 :
Take a Text Box and link it with J2 cell, where Love % calculated in previous worksheet.
Step 10 :
Go to previous Worksheet ("Calculation") and Select D2 cell. Link this D2 cell with B13 cell of "Love" Worksheet. So that whenever user enter their name in B13 cell of "Love" Worksheet, then it automatically placed in D2 cell of "Calculation" Worksheet.
Step 11 :
In the same way, go to previous Worksheet ("Calculation") and Select G2 cell. Link this G2 cell with J13 cell of "Love" Worksheet. So that whenever user enter their name in J13 cell of "Love" Worksheet, then it automatically placed in G2 cell of "Calculation" Worksheet.
Step 12 :
That's it. Save the workbook and enjoy the fun game.
Video Tutorial :If you want you can take help from my Video Tutorial uploaded in YouTube. Please SUBSCRIBE our YouTube Channel:
SUBSCRIBE our YouTube Channel:https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
Image 1: Valentine's Day Love Calculator in Excel
This is very easy to create it. But you need to concentrate on it. Here I've used very popular INDEX-MATCH Function and no VBA. So, let's going start.
Step 1:
Take a new workbook. and plot like below except Yellow background. Yellow background cells are containing formulas. I will describe the formula in next.
Image 2: Valentine's Day Love Calculator Fun Game Calculation
Step 2:
The D2 cell contains a name, here i used "Microsoft". It needs to convert into Numbers through A1:B6 data table. You can change as you like in A1:B6 data table. So, I have used below formula to convert the "Microsoft" into number as per character matching.
In D4 Cell, enter below formula and Copy it down to D23 cell.
=IFNA(INDEX($B$2:$B$6,MATCH(MID($D$2,ROWS($D$2:$D2),1),$A$2:$A$6,0)),0)
Step 3:
In the same way use the below formula in G4 cell and Copy it down to G23 cell:
=IFNA(INDEX($B$2:$B$6,MATCH(MID($G$2,ROWS($G$2:$G2),1),$A$2:$A$6,0)),0)
Step 4:
In E2 cell use the =SUM(D4:D23) formula to summarize the total number of "Microsoft" name.
Step 5:
In H2 cell use the =SUM(G4:G23) formula to summarize the total number of "Excel" name.
Step 6:
In J2 cell use the =IF((SUM(E2,H2)*1%)>=1,99%,SUM(E2,H2)*1%) formula to convert these 2 numbers (E2 and H2) into percentage (%).
Step 7:
Now take a New Worksheet and plot like below with AutoShape, Font and Text:
Image 3: Valentine's Day Love Calculator in Excel - Game desig
Step 8 :
Marge this B13:D14 cell so that, user can enter name in B13 cell. In the same way Marge the J13:L14 cell.
Step 9 :
Take a Text Box and link it with J2 cell, where Love % calculated in previous worksheet.
Step 10 :
Go to previous Worksheet ("Calculation") and Select D2 cell. Link this D2 cell with B13 cell of "Love" Worksheet. So that whenever user enter their name in B13 cell of "Love" Worksheet, then it automatically placed in D2 cell of "Calculation" Worksheet.
Step 11 :
In the same way, go to previous Worksheet ("Calculation") and Select G2 cell. Link this G2 cell with J13 cell of "Love" Worksheet. So that whenever user enter their name in J13 cell of "Love" Worksheet, then it automatically placed in G2 cell of "Calculation" Worksheet.
Step 12 :
That's it. Save the workbook and enjoy the fun game.
Video Tutorial :If you want you can take help from my Video Tutorial uploaded in YouTube. Please SUBSCRIBE our YouTube Channel:
SUBSCRIBE our YouTube Channel:https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw