Method for f(x)=y

**swedattack** · 06-11-2012, 03:32 AM

Hi all,

I have made a long function (multiple if variables, spanning a field of input numbers and percentages) to calculate the commission for a sales manager. Now what I am left with is basically one input, X, and one output, Y. Whenever I enter a department sales level (X) I get the correct commission (Y).

What I would like is to make use of this so that I can create a long list of various turnover numbers and show both in a table and with a graph how the commission would change with different amounts of sales. Is there anyway I can pull this off? Define the left column as X and have excel use the function to get a unique Y for each level?

**TMS** · 06-11-2012, 03:50 AM

If your values for x start in cell A2 and go down the column, you can put the formula, relating to A2 and giving y for that first value in B2. Then drag down. The cell reference to A2 should auto-adjust.

Regards, TMS

**swedattack** · 06-11-2012, 04:32 AM

Hey TMShucks

The function is a bit more complicated than that. I cannot think of any way to do it into one column.

The X value is called upon by a series of formulas, determining if the level is relevant for them, giving me a trinary if-variable response and on to a second column (another if formula) that decides the commission for that bracket of sales depending on a percentage. This gives me a series of numbers that are summarized into Y.

I need something that basically "puts" the X in the right spot and then "gathers" the Y, does that make sense?

**TMS** · 06-11-2012, 04:37 AM

I think a sample workbook would help to make more sense of it.

Regards, TMS

**swedattack** · 06-11-2012, 05:02 AM

Sent you a dropbox link

**TMS** · 06-11-2012, 05:56 AM

Mmmm ... not nice. Probably other and better ways but, for the moment, you could do it with a Worksheet_Change event.

Please Login or Register  to view this content.

You'd need to put "Monthly sales target" in, say, cell B20 as a heading and "Management commission:" in cell D20.

Now, every time you change B3, it will put that value and its result on the next available row. You could copy and paste the values somewhere else, if you wish.

An enhancement would be to put the Sales Targets in a list and cycle through them, dropping them into the calculation sheet.

Please Login or Register  to view this content.

So, it's doable but not necessarily aesthetically pleasing.

Regards, TMS

**swedattack** · 06-11-2012, 06:03 AM

Hey, thanks I'll try it out.
Need to be able to see how the commission changes as I alter the percentages, mess with the break points and so on, I'm OK wuth the code being ugly

Never used worksheet_change but I'll try it out. Thanks again!

**TMS** · 06-11-2012, 06:12 AM

I didn't say the code was ugly, just not aesthetically pleasing ... more the approach than the code.

You could probably set your list up and the first percentage, run the loop, change the percentage, run the loop, etc.

Regards, TMS

**swedattack** · 06-11-2012, 07:00 AM

Nope, my VB skills just aren't good enough. Can't make it work.

**TMS** · 06-11-2012, 07:03 AM

Please Login or Register  to view this content.

See attached updated workbook example.

Regards, TMS

**swedattack** · 06-11-2012, 07:16 AM

Think I got it!

I screwed up the definitions of what to define and nothing happened.

The second piece of code, the loop, was that to recalculate the output values (commission) after I change the formula info, for example if I set the first percentage to 0,2% instead of 0%?

**TMS** · 06-11-2012, 07:25 AM

The first piece of code was the change event which goes in the sheet module. That fires every time you change the Sales Target.

The second piece of code loops through a list of Sales Targets which fires the change event each time.

The third piece of code is a variation on the second piece of code. It loops through a list of percentages and, within that, loops through the list of Sales Targets. And it came with a working example.

Regards, TMS

**swedattack** · 06-11-2012, 07:44 AM

I managed to modify the example you sent me to give me percentage, sales, and commission in cells A,B,C 21 and down, every time you change the target percentage.

Can I make the commission (the output) change within the list as I alter the commission levels (columns B4-9). Meaning, a fixed list of sales (or piece of the target) and their corresponding commission amounts that change along with the formula?

**TMS** · 06-11-2012, 07:56 AM

You can have as many loops and variables as you wish ... but the last thing to change must be the Sales Target as that's what fires the Change event.

I would suggest that you just loop as you have done and then sort the output list into whatever sequence you'd like to see it.

Regards, TMS

**TMS** · 06-11-2012, 08:50 AM

Thanks for the rep. Solved?

**swedattack** · 06-12-2012, 11:17 PM

Doesn't really do what I wanted it to do, but I guess it is as close as we come with this method.

**TMS** · 06-13-2012, 01:22 AM

What can I say? You have a complex calculation for commission which goes over several lines. It's not a single formula that you put a value into and it churns out an answer.

There may well be other ways to crack this, there usually are, but my head is locked into this macro approach.

It could be worth your while starting a new thread, providing a link to this one, and asking for new ideas and suggestions.

Good luck, TMS

Method for f(x)=y

LinkBack

Thread Tools

Rate This Thread

Display

Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Re: Method for f(x)=y

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions