Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO WORKOUT PERCENTAGE
Hi, I have percentages table in Sheet1 (see below)
ROW A B ---col 1 GX GT ---codes 2 % % 3 8 0 4 8 25 5 9 0 6 8 0 7 8 25 8 9 0 9 8 0 10 8 25 11 9 0 12 8 0 13 8 25 14 9 0 I have another table in Shee2 (see below) in which column B I have amounts and in column A I'll put codes. ROW A B 1 CODE AMOUNT---headings 2 GX 2630 3 GT 2170 I want macro that when I put any code in column A of Sheet2 then it should multiply the amount figure of same row cell in column B whith the percentages given in Sheet1 under the exact code and produce result in Sheet3 and also the figures it will put on Sheet3 should be rounded. In Sheet3 macro should produce result something like this (see below) A B----col CODE AMOUNT GX 210.4 GX 210.4 GX 236.7 GX 210.4 GX 210.4 GX 236.7 GX 210.4 GX 210.4 GX 236.7 GX 210.4 GX 210.4 GX 236.7 GT 0 GT 542.5 GT 0 GT 0 GT 542.5 GT 0 GT 0 GT 542.5 GT 0 GT 0 GT 542.5 GT 0 but macro should also round the figures so result should be then (see below) A B----col CODE AMOUNT GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 The only problem by rounding will be the amounts different. If you total GX code figures on Sheet3 after rounding it will be 2628 but actual figure is 2630 on Sheet2 and same with GT code the total will be 2172 but actual figure on Sheet2 is 2170. by rounding figures it will not give the same figures so i want macro to when it will produce result on Sheet3 with the rounded figures then it should check the difference of actual amount of Sheet2 column B and the total of Sheet3 rounded figures under the same code and what ever the difference come like in above table its giving difference 0f 2 then it should take that away on put in on the last percentage figures on Sheet3. So the result should be then A B----col CODE AMOUNT GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 237 GX 210 GX 210 GX 239 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 543 GT 0 GT 0 GT 541 GT 0 if you see that the last figures of each code been changed by amount of 2. 237 to 239 and 543 to 541 I hope I explained what I am trying to say. Can anybody help please |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to workout the formula from many column.. | Excel Discussion (Misc queries) | |||
Trying to Create a Excel Spreadsheet that will tabulate workout ti | Excel Discussion (Misc queries) | |||
How do you set up a workout log? | New Users to Excel | |||
formula to workout equal to or greater than | Excel Worksheet Functions | |||
newbie trying to workout copy | Excel Programming |