Adding percentages that are produced by formulas

28creation

Board Regular
Joined
Oct 13, 2014
Messages
124
I can't believe you can't just add percentages with the usual =SUM(A1:A10) formula!!!

The percentages I want to add are all created by formulas, not typed into the cell itself. The total percentage at the bottom of the page is adding up the individual weightings from a number of entries.

How can this be done please?

Thanks in advance.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Do you have a column with the same number of percentages as what you're adding (10 in your example above)?
 
Last edited:
Upvote 0
I can't believe you can't just add percentages with the usual =SUM(A1:A10) formula!!
Of course you can. Whether the end result is meaningful depends on what those numbers represent and what you're trying to calculate.

If 50% of people own a car, and 30% own a house, you can't conclude that 80% own either a car or a house.
 
Last edited:
Upvote 0

Excel 2010
AB
13
27
350.225
43
52
610.2
790.525
89
980.05
103
11506.45
Sheet4
Cell Formulas
RangeFormula
A11=SUM(A1:A10)
B11=SUMPRODUCT(A1:A10,B1:B10)


where blanks are assumed to be zero. Is this close to what you're asking?
 
Upvote 0
Excel 2010
AB

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.225[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0.525[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0.05[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]6.45[/TD]

</tbody>
Sheet4

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A11[/TH]
[TD="align: left"]=SUM(A1:A10)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=SUMPRODUCT(A1:A10,B1:B10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



where blanks are assumed to be zero. Is this close to what you're asking?

It is, but it returns error #VALUE !
 
Upvote 0
You said the percentages are formula results, so they should be numbers not text. Can you show a sample of the data and formula?
 
Last edited:
Upvote 0
You said the percentages are formula results, so they should be numbers not text. Can you show a sample of the data and formula?




[TABLE="width: 342"]
<tbody>[TR]
[TD]EXCEEDING[/TD]
[TD]20%[/TD]
[TD]OUTSTANDING[/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8%[/TD]
[TD]6[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4%[/TD]
[TD]6[/TD]
[TD]4%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]5%[/TD]
[TD]1[/TD]
[TD]4%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8%[/TD]
[TD]2[/TD]
[TD]6%[/TD]
[/TR]
[TR]
[TD]PASS[/TD]
[TD]10%[/TD]
[TD]DEV[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]DEV[/TD]
[TD]5%[/TD]
[TD]FAIL[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]#VALUE ![/TD]
[TD] [/TD]
[TD]#VALUE ![/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

The first cell is O8.

The #VALUE ! cells at the bottom are P26 & R26.

The code in the #VALUE ! cells are

=SUMPRODUCT(P8:P9,P11:P15,P17:P18,P22:P24)

&

=SUMPRODUCT(R8:R9,R11:R15,R17:R18,R22:R24)

Thanks.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top