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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
219
SBExcel123
S
M
  • Solved
Split single cell value in 2 columns.
Replies
7
Views
360
Fluff
Fluff
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
280
Cquake
C
W
  • Question Question
Personnel Roster using Macro's
Replies
0
Views
326
womeclr
W

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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