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

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%) ?
--
David Biddulph

"The Fool on the Hill" wrote in
message ...
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

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" wrote:

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%) ?
--
David Biddulph

"The Fool on the Hill" wrote in
message ...
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

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.
--
David Biddulph

"The Fool on the Hill" wrote in
message ...
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" wrote:

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%) ?
--
David Biddulph

"The Fool on the Hill" wrote
in
message ...
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

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$2:D2)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


Replace @mailinator.com with @tiscali.co.uk


"The Fool on the Hill" wrote in
message ...
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

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.



"David Biddulph" wrote:

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.
--
David Biddulph

"The Fool on the Hill" wrote in
message ...
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" wrote:

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%) ?
--
David Biddulph

"The Fool on the Hill" wrote
in
message ...
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