Excel 2003 - Maximum Percentage formula...

[adamdc20]

Hi everyone - huge thanks in advance to anyone that can offer some assistance with this problem.

I have a 6x5 matrix with a series of data in it. It is as simple as this:

Row 13: 0 1 2 3 4
Row 14: 0 1 2 3 4

and this is repeated over for 6 rows. The figures in each cell can increase or decrease (dependant upon another multiplier elsewhere).

I need a formula to work out a % total for this data - but I really need each row to only contribute an equal amount (16.6r or 100/6)and when it reaches that maximum for it to contribute no more. (i.e - if rows 14, 15, 16, 17 and 18 are all 0 - I want the maximum that can come from Row 13 to be 16.7%)

If I simply SUM this data - I could potentially end up with 1000% total (if the figures increase then so will my % total) which would defeat the point.

I hope this makes some sense to someone and you'll be able to help. Please question me if it is not clear.

(Doubt it matters but I'm using Excel 2003)

 

[iliace]

So for example, assuming your data starts in column A, the formula for row 13 would be:

=MIN(SUM($A13:$E13),SUM($A$13:$E$18)/ROWS($A$13:$E$18))

Does that do what you want?

 

[adamdc20]

Thanks very much for the swift response.....

it may be doing the right thing - you wouldn't by any chance be able to explain what it is doing?

Thanks again

 

[iliace]

=MIN(SUM($A13:$E13),SUM($A$13:$E$18)/ROWS($A$13:$E$18))

SUM($A13:$E13) - gives you the row total
SUM($A$13:$E$18) - gives you the grand total
ROWS($A$13:$E$18) - divides grand total by the number of rows (in case you need to change it)

MIN takes the minimum of the two values.

 

[adamdc20]

Iliace - that does work to some extent so thanks very much for that!

One further issue that I have is that once I have calculated this for each row, I then need to SUM these figures to calculate an overall percentage for this area.

What I need to do is limit this so that it doesn't go over 100% - regardless of what figures are in each of the cells.......(almost to put an upper limit on each row?)

i.e - if cell A13 increases to 100 - I don't want this to mean that my final percentage goes to something like 114%.....I need to limit the impact of that row.

Is that possible?

Thanks ever so much in advance of your help!