Limiting within a formula

[adamdc20]

Thanks in advance to anyone who can help with this!!

I have a formula - which is giving me a percentage total - this is calculated by summing a series of totals from 6 rows of data i.e:

Row 1: 1 2 3 4 5 = 15
Row 2: 6 7 8 9 10 = 40
Row 3: 11 12 13 14 15 = 65
Row 4: 16 17 18 19 20 = 90

= Total - 210

(The figures are just examples - not the real data.)

The numbers in the original data can increase indefinitely.

I want to represent this total as a percentage (which I can do) - and I want to ensure that each row is only able to contribute a maximum to the total - 25% (i.e - If Row 1 total = 500, and all the other rows = 0. I want the maximum total % to be 25%. Is this possible?

What I am trying to avoid is having a 100% total - when only one of the rows is contributing.....

Thanks for your help - I realise this isn't the clearest but I hope it makes sense!

 

[schielrn]

I have no idea where your data is set up, but you can probably do something like:

=min(f1/total,.25)

F1 I have is where I assume 15 is in row 1. Then replace total wherever your total is. Hope that helps.

 

[adamdc20]

thanks so much - I've adapted your suggestion and applied it - it works fine!