Oct 01 2021 01:18 PM - edited Oct 01 2021 01:20 PM
I have 20 categories of Sales Revenue in Column A, the total revenue for each category is in column B, the % of a category, as it relates to the total categories is in column C but the Total Percentage is over 100% when I total all the percentages, what am I missing?
Amount | % of Sales | |||
Backup & Recover-Desktop Suppor | 70,808.50 | 2.2% | ||
Barracuda Back Up - Cloud to Cloud | 11,220.00 | 0.35% | ||
Barracuda ESS - Security Edition | $51,508.57 | 1.62% | ||
Barracuda Sentinel | 20,093.52 | 0.63% | ||
Carrier Management / NOC Support Services | $239,618.99 | 7.54% | ||
Cust Support Srvs/Provisioning/vCIO Svcs | $98,514.00 | 3.10% | ||
Field Services Support-Single | $1,447,941.00 | 45.56% | ||
FTE Tech | 159,227.50 | 5.01% | ||
Hardware Lease Revenue | 11,114.22 | 0.35% | ||
Help Desk Services | 1,604,649.85 | 50.49% | ||
IH-New User_Laptop Setup | $230,523.19 | 7.25% | ||
IH-Remote Work Support Services | 60,172.85 | 1.89% | ||
MRR - Data Services/Ticket Overage | $40,057.00 | 1.26% | ||
Network & Security Hardware | $985,825.90 | 31.02% | ||
Network Monitoring Service-Devices | 153,696.60 | 4.84% | ||
NRR - Carrier Management / NOC Services | 11,245.00 | 0.35% | ||
NRR - ESS Email Security Services | 28,865.00 | 0.91% | ||
NRR - Help Desk | 46,500.00 | 1.46% | ||
NRR - Migration Implementation | 98,595.00 | 3.10% | ||
NRR - Monitoring Services | 15,000.00 | 0.47% | ||
Remote Server Monitoring | 102,455.03 | 3.22% | ||
Remote Work/Sharepoint Migration | $84,026.55 | 2.64% | ||
Shipping & Delivery Fee | 49,970.63 | 1.57% | ||
Software/Licenses | 491,422.97 | 15.46% | ||
TOTAL | $3,178,015.20 | 192.35% |
Oct 04 2021 08:10 PM
Amount | % of Sales | ||
Backup & Recover-Desktop Suppor | 70,808.50 | 0.022 | 2.23% |
Barracuda Back Up - Cloud to Cloud | 11,220.00 | 0.0035 | 0.35% |
Barracuda ESS - Security Edition | 51,508.57 | 0.0162 | 1.62% |
Barracuda Sentinel | 20,093.52 | 0.0063 | 0.63% |
Carrier Management / NOC Support Services | 239,618.99 | 0.0754 | 7.54% |
Cust Support Srvs/Provisioning/vCIO Svcs | 98,514.00 | 0.031 | 3.10% |
Field Services Support-Single | 1,447,941.00 | 0.4556 | 45.56% |
FTE Tech | 159,227.50 | 0.0501 | 5.01% |
Hardware Lease Revenue | 11,114.22 | 0.0035 | 0.35% |
Help Desk Services | 1,604,649.85 | 0.5049 | 50.49% |
IH-New User_Laptop Setup | 230,523.19 | 0.0725 | 7.25% |
IH-Remote Work Support Services | 60,172.85 | 0.0189 | 1.89% |
MRR - Data Services/Ticket Overage | 40,057.00 | 0.0126 | 1.26% |
Network & Security Hardware | 985,825.90 | 0.3102 | 31.02% |
Network Monitoring Service-Devices | 153,696.60 | 0.0484 | 4.84% |
NRR - Carrier Management / NOC Services | 11,245.00 | 0.0035 | 0.35% |
NRR - ESS Email Security Services | 28,865.00 | 0.0091 | 0.91% |
NRR - Help Desk | 46,500.00 | 0.0146 | 1.46% |
NRR - Migration Implementation | 98,595.00 | 0.031 | 3.10% |
NRR - Monitoring Services | 15,000.00 | 0.0047 | 0.47% |
Remote Server Monitoring | 102,455.03 | 0.0322 | 3.22% |
Remote Work/Sharepoint Migration | 84,026.55 | 0.0264 | 2.64% |
Shipping & Delivery Fee | 49,970.63 | 0.0157 | 1.57% |
Software/Licenses | 491,422.97 | 0.1546 | 15.46% |
TOTAL | 3,178,015.20 | 1.9235 | 100.00% |
The formula I used is: =C2/$C$26
all the way down including the Total to get the 100%
don't sum the % column
cheers
Oct 04 2021 08:44 PM
Oct 04 2021 09:52 PM
@Nathalie135 Not sure about your arithmetic. When I add-up the sales amounts, I come to just over 6.1 million. I.e. not 3.2 million. That's a factor 1.9235 higher. That's why you get the wrong overall percentage. And, of course, you may add up individual percentages, provided you calculated them on the correct basis.
Oct 05 2021 07:04 AM
Oct 05 2021 08:10 AM
@Nathalie135.... FYI, "cell formatting" cannot explain the difference between 3.2 million versus 6.1 million.
A more likely explanation is: the summation cell was not recalculated. And beware: that might still be an ongoing issue, remedied on a one-time basis because the changes that you made forced a recalculation.
In the future, attach a example Excel file that demonstrates the problem so that we can see your formulas, the original data, and the settings of workbook-specific options.
Oct 05 2021 08:15 AM