Automatically calculate percentages in a spreadsheet
Posted by Bill Brown on June 27, 2001 6:33 AM
I want to create a spreadsheet with the following fields: invoice amount, category, company a, company b, company c. I then want my user to be able to enter an invoice amount and a catergory, then have the spreadsheet automatically calculate the amounts that companies a, b, and c owe. There will be four different categories, and each company will owe a different percentage depending upon which category is entered by my user. So, for example if the invoice is $5,000, and the category is 1, then A owes 25%, B owes 37%, and C owes 38%. But, if the category is 2, then A owes 50%, B owes 50%, and C doesn't owe anything. Again there will be four categories. Any help you can offer will be greatly appreciated!
Thank you,
Bill Brown
Posted by Russell on June 27, 2001 7:10 AM
This is how I would do it....
I would put the percentages on a different sheet in a table, then VLOOKUP to them. For example, the table might look like this:
CATEGORY CompA CompB CompC
1 .25 .37 .38
2 .50 .25 .25
3 .00 .50 .50
4 .18 .17 .65
Then on your sheet where you want the calculations, you could have something like this:
InvAmt Category CompA CompB CompC
(and these 5 columns are in columns A,B,C,D,and E)
So your user would type in the first 2 columns. In the third column (C - for Company A) you would have this formula (say we're in row 2):
=$A1*VLOOKUP($B2, LookupSheet!$A$1:$D$4, 2, FALSE)
Where LookupSheet is the name of the sheet where you put your table with the percentages (in the area from A1 to D4).
For company B, the formula would be:
=$A1*VLOOKUP($B2, LookupSheet!$A$1:$D$4, 3, FALSE)
And for company C you would change the 3 to a 4. Then you could just drag these formulas down....
Hope this helps,
Russell
(Email me if you would like me to send you a sample of how this would work).
