automate %age for a table

[azii]

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.
Please help

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>

 

[jarjarbingie]

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

 

[azii]

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>

 

[jarjarbingie]

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

 

[azii]

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.