# automate %age for a table

#### azii

##### Board Regular
Dear all
this is the table that I have to take %age of all "Trans name". Under One Sale Point there are many Trans names. (e.g. in Burewala Total means I want to take %age of MG total (i.e. 70/120*100). please help me to automate the formula as this table has more than 3000 rows to calculate the %age.
I need %age in front of every trans name after "qty" in "%age" column.

 Sale Point Trans Name Qty %age MG Total 70.00 RE Total 50.00 BUREWALA Total 120.00 AM Total 180.00 AN Total 60.00 FM Total 65.00 IC Total 60.00 MB Total 590.00 ME Total 130.00 MG Total 610.00 PC Total 430.00 RE Total 365.00 SB Total 170.00 SG Total 520.00 SHI Total 250.00 TA Total 195.00 WB Total 350.00 WWC Total 100.00 GAGOO MANDI Total 4075.00 AN Total 70.00 GE Total 70.00 ME Total 140.00 RE Total 140.00 SG Total 70.00 TA Total 70.00 WWC Total 140.00 BUREWALA Total 700.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
say, the table you have above is located in cell A1:C28,

D2 = C2/OFFSET(\$C\$1,MIN(IF(B2:B28="",ROW(B2:B28)-1,"")),0) then press CTRL+SHIFT+ENTER..

Dear jarjarbingie
It was excellent work, but I made a mistake, data is actually look like this. Every "Sale Point" has a total, under "Sale Point" there are "Trans name" these trans name comes more than one time. e.g. "Trans Name" AM come 4 time in Gagoo mandi "Sale Point" and then Comes AM Total. your formula is excellent piece of work, but it is confusing me when apply in the given below table. please help. to understand and application of the formula.

Really greatfull to you for such a nice formula.

 Sale Point Trans Name Qty %age BUREWALA MG 70.00 MG Total 70.00 BUREWALA RE 50.00 RE Total 50.00 BUREWALA Total 120.00 GAGOO MANDI AM 70.00 GAGOO MANDI AM 50.00 GAGOO MANDI AM 10.00 GAGOO MANDI AM 50.00 AM Total 180.00 GAGOO MANDI AN 60.00 AN Total 60.00 GAGOO MANDI FM 65.00 FM Total 65.00 GAGOO MANDI IC 60.00 IC Total 60.00 GAGOO MANDI MB 70.00 GAGOO MANDI MB 70.00 GAGOO MANDI MB 50.00 GAGOO MANDI MB 20.00 GAGOO MANDI MB 10.00 GAGOO MANDI MB 50.00 GAGOO MANDI MB 50.00 GAGOO MANDI MB 10.00 GAGOO MANDI MB 10.00 GAGOO MANDI MB 50.00 GAGOO MANDI MB 10.00 GAGOO MANDI MB 50.00 GAGOO MANDI MB 20.00 GAGOO MANDI MB 60.00 GAGOO MANDI MB 60.00 MB Total 590.00 GAGOO MANDI ME 60.00 GAGOO MANDI ME 10.00 GAGOO MANDI ME 10.00 GAGOO MANDI ME 50.00 ME Total 130.00 GAGOO MANDI MG 70.00 GAGOO MANDI MG 60.00 GAGOO MANDI MG 50.00 GAGOO MANDI MG 10.00 GAGOO MANDI MG 40.00 GAGOO MANDI MG 50.00 GAGOO MANDI MG 50.00 GAGOO MANDI MG 20.00 GAGOO MANDI MG 70.00 GAGOO MANDI MG 70.00 GAGOO MANDI MG 70.00 GAGOO MANDI MG 50.00 MG Total 610.00 GAGOO MANDI PC 70.00 GAGOO MANDI PC 50.00 GAGOO MANDI PC 30.00 GAGOO MANDI PC 20.00 GAGOO MANDI PC 50.00 GAGOO MANDI PC 50.00 GAGOO MANDI PC 20.00 GAGOO MANDI PC 70.00 GAGOO MANDI PC 70.00 PC Total 430.00 GAGOO MANDI RE 50.00 GAGOO MANDI RE 10.00 GAGOO MANDI RE 10.00 GAGOO MANDI RE 35.00 GAGOO MANDI RE 30.00 GAGOO MANDI RE 30.00 GAGOO MANDI RE 10.00 GAGOO MANDI RE 50.00 GAGOO MANDI RE 20.00 GAGOO MANDI RE 60.00 GAGOO MANDI RE 60.00 RE Total 365.00 GAGOO MANDI SB 40.00 GAGOO MANDI SB 30.00 GAGOO MANDI SB 20.00 GAGOO MANDI SB 10.00 GAGOO MANDI SB 50.00 GAGOO MANDI SB 20.00 SB Total 170.00 GAGOO MANDI SG 70.00 GAGOO MANDI SG 70.00 GAGOO MANDI SG 50.00 GAGOO MANDI SG 20.00 GAGOO MANDI SG 50.00 GAGOO MANDI SG 20.00 GAGOO MANDI SG 50.00 GAGOO MANDI SG 10.00 GAGOO MANDI SG 50.00 GAGOO MANDI SG 10.00 GAGOO MANDI SG 60.00 GAGOO MANDI SG 50.00 GAGOO MANDI SG 10.00 SG Total 520.00 GAGOO MANDI SHI 70.00 GAGOO MANDI SHI 50.00 GAGOO MANDI SHI 20.00 GAGOO MANDI SHI 10.00 GAGOO MANDI SHI 40.00 GAGOO MANDI SHI 50.00 GAGOO MANDI SHI 10.00 SHI Total 250.00 GAGOO MANDI TA 65.00 GAGOO MANDI TA 50.00 GAGOO MANDI TA 10.00 GAGOO MANDI TA 70.00 TA Total 195.00 GAGOO MANDI WB 60.00 GAGOO MANDI WB 30.00 GAGOO MANDI WB 50.00 GAGOO MANDI WB 50.00 GAGOO MANDI WB 50.00 GAGOO MANDI WB 15.00 GAGOO MANDI WB 35.00 GAGOO MANDI WB 50.00 GAGOO MANDI WB 10.00 WB Total 350.00 GAGOO MANDI WWC 50.00 GAGOO MANDI WWC 50.00 WWC Total 100.00 GAGOO MANDI Total 4075.00 BUREWALA AN 70.00 AN Total 70.00 BUREWALA GE 70.00 GE Total 70.00 BUREWALA ME 70.00 BUREWALA ME 70.00 ME Total 140.00 BUREWALA RE 70.00 BUREWALA RE 70.00 RE Total 140.00 BUREWALA SG 70.00 SG Total 70.00 BUREWALA TA 70.00 TA Total 70.00 BUREWALA WWC 70.00 BUREWALA WWC 70.00 WWC Total 140.00 BUREWALA Total 700.00 KHANEWAL AM 70.00 KHANEWAL AM 25.00 KHANEWAL AM 25.00 AM Total 120.00 KHANEWAL AN 50.00 KHANEWAL AN 50.00 AN Total 100.00 KHANEWAL FM 70.00 KHANEWAL FM 70.00 KHANEWAL FM 50.00 KHANEWAL FM 50.00 FM Total 240.00 KHANEWAL IC 50.00 IC Total 50.00 KHANEWAL ME 70.00 KHANEWAL ME 70.00 KHANEWAL ME 50.00 ME Total 190.00 KHANEWAL MG 70.00 KHANEWAL MG 70.00 KHANEWAL MG 70.00 KHANEWAL MG 50.00 KHANEWAL MG 50.00 KHANEWAL MG 10.00 MG Total 320.00 KHANEWAL PC 50.00 KHANEWAL PC 10.00 PC Total 60.00 KHANEWAL RE 70.00 KHANEWAL RE 50.00 KHANEWAL RE 50.00 RE Total 170.00 KHANEWAL SB 5.00 SB Total 5.00 KHANEWAL SG 45.00 KHANEWAL SG 70.00 KHANEWAL SG 70.00 KHANEWAL SG 50.00 SG Total 235.00 KHANEWAL SHI 10.00 KHANEWAL SHI 10.00 KHANEWAL SHI 50.00 KHANEWAL SHI 20.00 KHANEWAL SHI 45.00 SHI Total 135.00 KHANEWAL TA 70.00 KHANEWAL TA 70.00 KHANEWAL TA 70.00 TA Total 210.00 KHANEWAL WB 30.00 KHANEWAL WB 20.00 KHANEWAL WB 35.00 KHANEWAL WB 70.00 KHANEWAL WB 70.00 KHANEWAL WB 5.00 WB Total 230.00 KHANEWAL WWC 70.00 KHANEWAL WWC 70.00 WWC Total 140.00 KHANEWAL Total 2205.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

should the percentage be based on the TRANS NAME subtotals or SALE POINT subtotals? and is this from a pivot table? if yes, you can just opt to remove the subtotals in the TRANS NAME field and just use the formula above..

Dear
It was my mistake. I couldn't explain it to you. your formula is great and it has done all my needs. anyways this table is not a pivot table. all the percentage based on the "Sale Point" and I have to calculate the "tans name" share (i.e. AM total not AM)
I am very thankful to you for such a comprehensive formula.

Great Work.
Keep it up (sharing the knowledge to the world...!).

Thanks again.

Replies
2
Views
371
Replies
7
Views
997
Replies
0
Views
458
Replies
5
Views
4K

1,196,216
Messages
6,014,043
Members
441,801
Latest member
Aneurysm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back