Help!

Question

I am struggling to work this out in my head so bare with me as I try to type out what I need help with :)

I am trying to do a total calculation of data of separate sheets. Sheet 1 has total points awarded to a person.

Sheet 2 - has a point system table. For example: 1 point = 0, 2 points = 4.

I am trying to create a separate calculation sheet that calculates both sheets. Essentially, I want for all data in sheet 1 to pickup the data from sheet 2 and calculate.

Answer 1

Dear FranTiatoa

How are things? We have not heard from you since our last response. You can submit your feedback by clicking "Yes" or "No" below the valid answers. We value your feedback, which is very important for the rating of our customers. In addition, this can also give back to the community and help more people with the same problem quickly locate that topic.

If you have any updates, please let us know at any time. Thank you for your understanding and cooperation. I wish you a happy life!

Best regards

Adrian.C-MSFT | Microsoft Community Support Specialist

Answer 2

Hi FranTiatoa

Welcome to Microsoft Community.

I realize that you've encountered a problem with " Excel Calculates Data ", and I understand how you feel. Don't worry, I'll do my best to help you.

Based on the data provided and the scoring rules, here is how the total score is calculated:

Step-by-Step Explanation:

1. Adjust the Points Table Structure (for easy formula reference):

| Score | Points | 

|---|---| 

| 1 | 0 | 

| 2 | 4 | 

| 3 | 5 | 

| 4 | 8 | 

1. Convert Daily Scores to Points for Each Task:
   • Use the VLOOKUP function to map scores from Sheet 1 to their corresponding points in Sheet 2.
     Example Formula (assuming Sheet 2 is in columns A:B ):

       =VLOOKUP(Score_Cell, Sheet2!A:B, 2, FALSE)
     

2. Handle Empty Cells:
   Treat blank cells as 0 points by modifying the formula:

    =IF(ISBLANK(Score_Cell), 0, VLOOKUP(...))
   

3. Calculate Total Points:
   Sum all converted points.

---

Example Calculation (Based on Sample Data):

• Brush Teeth:
  Mon = 3 → 5 points, Tues = 1 → 0 points → Total = 5 points
• Make Bed:
  Mon = 3 → 5 points, Tues = 3 → 5 points → Total = 10 points
• Vacuum Floor:
  Mon = 2 → 4 points, Tues = 3 → 5 points → Total = 9 points

Total Points = 5 + 10 + 9 = 24 points

---

Final Answer:

Total Points:
24 points

In addition, during the calculation process, I found that the calculation rules in your picture seemed to be somewhat inconsistent with the data you provided, but I moved it closer to the correct direction, so if I have any misunderstandings or you have anything to add, please let me know in your reply.

I hope these suggestions have been helpful to you!

Feel free to let me know if you have any other questions or need any further help.

Best regards

Adrian.C-MSFT | Microsoft Community Support Specialist