Split %age

S

Salamullah

Board Regular
Joined
Mar 28, 2011
Messages
221
Dear Excel fellows:

I want formula in Column C to E based on the percentage of column A on the quantity available in B, please help

[TABLE="width: 390"]
<colgroup><col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 6729;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" span="4"> <tbody>[TR]
[TD="class: xl65, width: 184, bgcolor: transparent"]Split %[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]Quantity[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]P[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]M[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]A 50% P 50%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent"] 5 [/TD]
[TD="class: xl67, bgcolor: transparent"] 5 [/TD]
[TD="class: xl67, bgcolor: transparent"] - [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]A 100%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl67, bgcolor: transparent"] 20 [/TD]
[TD="class: xl67, bgcolor: transparent"] - [/TD]
[TD="class: xl67, bgcolor: transparent"] - [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]P 100%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, bgcolor: transparent"] - [/TD]
[TD="class: xl67, bgcolor: transparent"] 40 [/TD]
[TD="class: xl67, bgcolor: transparent"] - [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]M 33.33% A 33.33% P 33.33%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl67, bgcolor: transparent"] 10 [/TD]
[TD="class: xl67, bgcolor: transparent"] 10 [/TD]
[TD="class: xl67, bgcolor: transparent"] 10 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]M 75% A 25%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, bgcolor: transparent"] 10 [/TD]
[TD="class: xl67, bgcolor: transparent"] - [/TD]
[TD="class: xl67, bgcolor: transparent"] 30 [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hi njimack - Excellent !! many thanks for the quick solution.

grateful if you can help me to change the formula on changing quantity into 3 column as follow:

[TABLE="width: 538"]
<colgroup><col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 6729;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" span="4"> <tbody>[TR]
[TD="class: xl65, width: 184, bgcolor: transparent"]Split %[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]excess stock[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]slow moving[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]Obsolete[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]P[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]M[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]A 50% P 50%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]A 100%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]P 100%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]M 33.33% A 33.33% P 33.33%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]M 75% A 25%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
What are the rules that you've applied? Why does the 3rd row only add up to 50 rather than 60?

Salamullah said:
Hi njimack - Excellent !! many thanks for the quick solution.

grateful if you can help me to change the formula on changing quantity into 3 column as follow:

[TABLE="width: 538"]
<tbody>[TR]
[TD="class: xl65, width: 184, bgcolor: transparent"]Split %[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]excess stock[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]slow moving[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]Obsolete[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]P[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]M[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]A 50% P 50%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]A 100%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]P 100%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]M 33.33% A 33.33% P 33.33%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]M 75% A 25%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
njimack said:
What are the rules that you've applied? Why does the 3rd row only add up to 50 rather than 60?
Click to expand...

Sorry its my mistake its 60 not 50.

I have applied sum starting to your given formula which is giving me the desired result, if you have better than this please share.

=sum($B2:$D2)*IFERROR(MID($A2,FIND(C$1,$A2)+2,FIND("%",$A2,FIND(C$1,$A2))-FIND(C$1,$A2)),0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
C
  • Locked
  • Question Question
Tex Split
Replies
4
Views
93
RoryA
RoryA
M
  • Solved
Split single cell value in 2 columns.
Replies
7
Views
366
Fluff
Fluff
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
470
Sundance_Kid
S
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
297
Cquake
C

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top