Adding up percentages

[Guest]

Dear Excel(lent) users,

I want to add up percentages:

I have 4 columns (A, B, C, D). In
A: Name of a person (selectable from validation list)
B: Percentage
C: Name of a person (selectable from validation list, same list as A)
D: Percentage

You can select a person and add a percentage. This means that one person can
be selected multiple times.

I know want to control, that one person is not going to exceed 100%. Is
there some formula for this?

Thanks for helping me out !

Kind regards,

Jay

 

[David Biddulph]

Well, you haven't told us what formula you are using for adding the
percentages, but try =MIN(your_formula,1) or =MIN(your_formula,100%) ?

 

[Guest]

Hello David,

The problem is that I do not have a formula, that is why I am seeking your
help. I am sorry if I haven;'t been clear enough!.

Kind regards,

Jay

 

[David Biddulph]

Sorry, I can't guess the answer if I can't guess your question. I'll leave
it to those who have a more powerful crystal ball than I have.

 

[Sandy Mann]

Jay,

If I understand you correctly:

Highlight the cells in Column B and select: Format > Conditional formatting

click the arrowhead in the "Cell Value Is" and select "Formula Is" then

enter the formula:

=SUMPRODUCT((B2<>"")*(SUM($B$2:B2,$D$22)>1)*($A2=A2))

Click on the "Format" button and select "Patterns" and select the colour you
want. Click OK > OK.

Highlight Column D cells and repeat the above but use the formula:

=SUMPRODUCT((D2<>"")*(SUM($B$2:B2,$D$2:$D$2)>1)*($A2=A2))

If you enter the percentages for any person that adds up to more that 100%
then that cell that takes you over 100% will be highlighted with the colour
you selected.

If you would rather prevent people entering the value then use the above
method but select Validation form the Data menu instead of Conditional
formatting form the Format menu.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Ok OK, I was not clear enough. Please allow me to be more clear.

When in Column A a name is selected and a percentage is added in Column B
And you continue filling the details down the column. The percentages can be
added up. In a different field I want to check whether a person does not
exceed the 100% mark.

For instance:
A B C D
-------------------------------------------
Person 1 1 5% Person 2 10%
Person 2 25% Person 2 10%
Person 3 35% Person 1 10%
Person 4 45% Person 3 10%
Person 2 55% Person 4 10%

Now in a different column I want to check if for instance person 2 does not
exceed 100%. Now the formula needs to check All person 1's in A and C and
check what percentages they have behind their name and sum them up.
In this case person 2 occurs 4 times (2x in A and 2x in B) and his sumned
percentage is 25+55+10+10=100% (in this case it is ok).

Does this make more sense??

Sorry for any inconvenience caused.