MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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).