Formula or VBA for auto changing of qty based on a Value

R

RAMU

Active Member
Joined
Dec 11, 2009
Messages
345
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
Dear Friends,

Suppose I have a data like the following:

[TABLE="width: 292"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]FRUITS[/TD]
[TD]QTY[/TD]
[TD]RATE[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD]GUAVA[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]5395[/TD]
[/TR]
[TR]
[TD]ORANGE[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]520[/TD]
[/TR]
[TR]
[TD]BANANA[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]390[/TD]
[/TR]
[TR]
[TD]GRAPE[/TD]
[TD="align: right"]75.25[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]3386.25[/TD]
[/TR]
[TR]
[TD]LEAMON[/TD]
[TD="align: right"]32.5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]487.5[/TD]
[/TR]
[TR]
[TD]LITCHI[/TD]
[TD="align: right"]38.52[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1155.6[/TD]
[/TR]
[TR]
[TD]DATES[/TD]
[TD="align: right"]56.75[/TD]
[TD="align: right"]62.5[/TD]
[TD="align: right"]3546.875[/TD]
[/TR]
[TR]
[TD]CHERRY[/TD]
[TD="align: right"]23.2[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]649.6[/TD]
[/TR]
[TR]
[TD]PAPAYA[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]416[/TD]
[/TR]
[TR]
[TD]COCONUT[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GRAND TOTAL[/TD]
[TD="align: right"]21246.83[/TD]
[/TR]
</tbody>[/TABLE]

Total column is simple multiplication of Rate & Qty and Grand Total is normal SUM formula.

"FRUIT" is in A1 & "21246.83" is in D13. In G13 I have put the same figure of Grand Total i.e. 21246.83 manually.

What I need basically a formula in a column or VBA by which if I change the value of G13, Constant the rates the qty of all fruits will be changed automatically on pro rata basis or percentage basis of total qty of fruits.

Thanks in advance.
Regards
RAMU
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Place this formula in cell E2 and copy to cell E12:
Code: 
=B2*($G$13/$D$13)

I think that should give you what you want.
 
Upvote 0
Joe4 said:
Place this formula in cell E2 and copy to cell E12:
Code: 
=B2*($G$13/$D$13)

I think that should give you what you want.
Click to expand...

Hi Joe,

Gr8...!!! It works perfect

Regards
RAMU
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

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