Calculation issues.

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
236
Office Version
2016
Platform
Windows
I am having a hell of an issue that i can not figure out. So I have 6 teams with three months of data I need to track. Each team has a goal they need to achieve each month based on how many people are on that team. This goal changes with the number of people on the team. This is at the bottom of each team. The number of what they need to get for the three months is to the right of that highlighted in RED. But here is where my issue springs up. The numbers for all three months do not add up. 15+15+8=38 but I get 39

When I add a name on the team below (Purple) the top team the number changes to 37. This happens with all the teams except for the last one for some reason.

I can not figure out what is going on but i have added the spreadsheet so it can be seen for yourself. Thank you for your help in advance.
https://www.dropbox.com/s/n1vv39il6ogo8iw/NOT ADDING UP.xlsx?dl=0
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,914
Office Version
365, 2010
Platform
Windows, Mobile
It is because your cells are formatted to not show the decimal spaces, your values are actually...
15.4761904761905
15.4761904761905
and
7.85714285714286

giving a total of

38.8095238095238

also formatted to no decimal places so will round up visually to 39
 
Last edited:

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
236
Office Version
2016
Platform
Windows
Is there a way to correct this? I attempted to change the format but get the same results.
Added why is it when i ad a name to another team the number changes to a lower number?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,914
Office Version
365, 2010
Platform
Windows, Mobile
I attempted to change the format but get the same results.
Changing the format does not change the underlying number (think of it like putting a filter over a picture, how you perceive it changes but it doesn't alter the actual picture).

The simple way is to use Round i.e. for cell D22 change the formula to =ROUND(G153,0) and do the same for E22 and F22 if you want the result to be 38.

Btw, correct is not the right term to use as Excel was giving the correct answer with the data you had there.

As for your other question upload what you have done as I haven't looked at the rest of your spreadsheet.
 
Last edited:

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
236
Office Version
2016
Platform
Windows
All I have done was try the different formattings, nothing major was done. I am attempting to use your method now.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,914
Office Version
365, 2010
Platform
Windows, Mobile
All I have done was try the different formattings, nothing major was done
You must have done changed something or the below question doesn't make any sense ;)

why is it when i ad a name to another team the number changes to a lower number?
 
Last edited:

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
236
Office Version
2016
Platform
Windows
Oh...ok. So based of the original spreadsheet I sent, putting a name in the second teams box changes the first teams number to 37 and not 39. This is what i was referring to. I don't understand how the team below would affect the the sum of the team aboves total sum of the three months. If you give it a try it might make what i am saying a better clearer. I hope.

I would seem that I will have to do the round for all the cells for it work. I am almost finish and will send a link shortly. Thank you for your help
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,914
Office Version
365, 2010
Platform
Windows, Mobile
It will work for the sum part.
If you want to see why the rest is affected click E22 then on the formulas tab click trace precedents, then when you have followed the arrows, clicking the cell the arrow points to at each stage click trace precedents until you get to k151 then add a team and you will see what sums affect it (remembering how you have the cells formatted).
 
Last edited:

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
236
Office Version
2016
Platform
Windows
Thank you for your time and help by the way
 

Watch MrExcel Video

Forum statistics

Threads
1,102,872
Messages
5,489,405
Members
407,687
Latest member
NeoSez

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top