• 0

Excel Spreadsheet Question


Question

OK, well, I work on commission, and long story short, they screwed up my commission and said that they were going to fix it. So anyway, they gave me a commission sheet which has all my sales on it. So, we get different percents for different divisions (for instance Division 36 Items = 10% Commission) So, how can I get it so that I can enter: Price of the Item, Divison, Commission - and have it recognize different division numbers as percents and then calculate the commission?

Link to comment
https://www.neowin.net/forum/topic/404464-excel-spreadsheet-question/
Share on other sites

8 answers to this question

Recommended Posts

  • 0
  dreamz said:

excel is an extremely powerful application. i use it every day at work.

here's an example of the sumif function: http://www.techonthenet.com/excel/formulas/sumif.php

Most of my knowledge of Excel comes from coding Macros in Visual Basic for it. I agree with you, ex-TREME-ly powerful :yes:

@ninjamunky:

If you can give us a list of Divisions and their equal percentages, I'll write you up a formula.

  • 0

OK, I have to go to work, I'll post back later today with that then. Thanks a lot :D

36 - 10%

54 - 10%

67 - 6%

76 - 6%

That's pretty much it, although some of those seem low. I know in the one we generally only get 6% (because there are categories within the division). I'll adjust the averages (because some are like 8-10% for example, so 9% would be more appropriate)

Again, thanks a lot for helping out.

Edited by ninjamunky
  • 0

There are really two different ways to do this, using the IF function or using the LOOKUP function, and they both work equally well. If you use the IF function, you will have to nest a few additional IF functions in the formula. If you use the LOOKUP function, you will have to list all the possible division numbers and the possible commission in the function. The reason I am presenting both ways of doing this is because the two functions don't always serve the same purpose. IF is technically used for comparison, and it has a limit of 7 nested IF functions. If you have more than that, you are out of luck. LOOKUP doesn't have this limitation, so if you would have had 20 different divisions, IF would not work, but LOOKUP would.

Here is how you do it. Assume you have the values 36, 54, 67, and 76 in column A, in the range A1:A4. In column B, cell B1, you would have one of the following two formulas:

=IF(A1=36,0.1,IF(A1=54,0.1,IF(A1=67,0.06,0.06)))

=LOOKUP(A1,{36,54,67,76},{0.1,0.1,0.06,0.06})

Both of these formulas can be copied down the column and the value will change accordingly. I am attaching two screenshots to show the formulas in action. Note that the results of the two different formulas are identical in columns B and C.

IF function at work (you can see the formula in the formula bar)

post-5076-1133736406.png

LOOKUP function at work (you can see the formula in the formula bar)

post-5076-1133736519.png

After you get the formulas working, just hit the percentage sign button on the toolbar to get your cells as percentages (or you can use the Format Cells feature to change the number type into Percentage, from the default General).

Edited by Hawkeye
  • 0

I threw this spreadsheet together for you.

After some thought, I decided a VLOOKUP (searches a verticle table of information for a matching result) was more appropriate, and allowed percentages/divisions to be changed.

I've included extra room for you to include more divisions and their matching percentage as well.

EDIT: Took too long writing this post. Hawkeye's methods are both correct. Here is a screenshot showing this spreadsheet.

post-42104-1133738116_thumb.jpg

And the formula I used is:

=IF(ISNA(VLOOKUP(C2,H4:I22,2) ),0,VLOOKUP(C2,H4:I22,2) )

Divisions.zipFetching info...

Edited by Chode
This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.