Need formula to get the Market share

Im enclosing a sample data file with comments mentioned in column headings. I just want to adjust the percentages shares in such manner that the their total comes to 100%

this looks like it might work...
=(D5/SUM($D$5:$D$14))/100*100
in cell E5 and dragged down.
see if that gets you what you want.

Thanks for your prompt reply but please note that in case of actual share of a company is less than 0.5% its share will be fixed at 0.5%. In view of foregoing, your proposed formula doesn't give the desired result.

well you didn't include expected results and the total of that series of cells is 100%.
So I'm guessing that you want the minimum to be the 0.500% and the adjustment to be taken from the cells above it that are companies A through D, and E through J stay the same, is that correct?

Yes exactly

Not sure about this one, but try

Formula:

Please Login or Register  to view this content.

It totals 100%.

Thanks FlameRetired for your response & suggested formula. I have enclosed a set of data and applied your proposed formula to the data set. In cell E15 it gives a result 0.475% however, it may be noted that no value in column E should be less than 0.5% and rest of the results adjusted accordingly so that sum of values of E15 comes to 100%.

Withdrawn by FR. Doesn't work.

Also withdrawn by FR. Doesn't work.

Let me rephrase the problem and enclosing a sample data

I have a set of sales data of our dealers. Based on the sales data I have worked out their % share. My Objective is to get a security balance of each dealer based following conditions

i. Total Security Amount is $ 52,562.
ii. The minimum contribution by every dealer shold not be less than 0.5%
iii. Any dealer having a share less than 0.5% will contribute 0.5% (their share will be treated as 0.5% as minimum contribution).
iv. The dealers whose share is more that 0.5% will contribute according to their share. However, their shares to be adjusted in such manner that the sum of all dealers contribution share is equal to 100%

Sample data is enclosed.

Put this in F3 and drag down:

Formula:

Please Login or Register  to view this content.

Alternative version which does the same thing:

Formula:

Please Login or Register  to view this content.

Depending on your regional settings, you may need to replace each comma (,) with a semi-colon (;).

How it works:
If the % share is less than 0.5% it will return 0.5%.
The other figures are adjusted like this:
1. Sum the %shares greater than or equal to 0.5% (in your sample figures this comes to 99.306%);
2. Divide the %share by that summed figure;
3. Count the %shares less than 0.5% and multiply by 0.5% (in your sample figures there are four, giving 2%);
4. Subtract the result of (4) from 100% (in your sample figures this gives 98%);
5. Multiple the result of (2) by the result of (4).
In other words, for your sample data each %share greater than or equal to 0.5% is divided by 99.306 and multiplied by 98.



Hope that makes sense and does what you need.




Edit: the formulae above are for your second sample file. For your first, they would be:

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

E3

Please Login or Register  to view this content.

Try this and copy towards down

Dear Aardigspook, samba_ravi, and Phuocam thanks for your time and suggestions. all your proposed formulas works fine and gives the desired solution.

You're welcome. Glad we could help and thanks for the rep.