#### How do I determine

```I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to =A31,000 worth of products, I will pay them 3% of the
value.

Between =A31,000 & =A32,000, I will pay them 5% of the value

Over =A32,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

darren.haste@ttt.co.uk
```
 0
6/25/2008 8:59:12 AM
excel 39879 articles. 2 followers.

11 Replies
872 Views

Similar Articles

[PageSpeed] 25

```"Hastey" <darrenhaste@sky.com> wrote in message
I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to �1,000 worth of products, I will pay them 3% of the
value.

Between �1,000 & �2,000, I will pay them 5% of the value

Over �2,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

darren.haste@ttt.co.uk

You need to be aware of a perennial trap when calculating days difference.
The answer lies in if you are counting gross or net days.

In a simple example, if a person starts work on 1 January and ends work on 2
January, 2 days pay would be due.  Calculating the number of days pay by
taking the last day less the first day would result in an underpayment by
one day.  It is, therefore, important when calculating gross days to add one
day (i.e. =date2-date1+1)

Bill Ridgeway

Computer Solutions

```
 0
Bill
6/25/2008 9:47:02 AM
```Oops! apologies

Regards.

Bill Ridgeway
Computer Solutions

```
 0
Bill
6/25/2008 9:48:37 AM
```Try this:

=3DIF(A2<=3D1000,3%,IF(A2<=3D2000,5%,10%))*A2

Hope this helps.

Pete

On Jun 25, 9:59=A0am, Hastey <darrenha...@sky.com> wrote:
> I have X number of rows that show employee names and a column (A2)
> that lists the value of products sold by each employee and I want to
> pay them a percentage based upon value of sales. For example:
>
> If they sell up to =A31,000 worth of products, I will pay them 3% of the
> value.
>
> Between =A31,000 & =A32,000, I will pay them 5% of the value
>
> Over =A32,000, I will pay them 10% of the value
>
> The amount paid to them in commission will be show in column (A3).
>
> Many thanks
>
> D.Haste
>
> darren.ha...@ttt.co.uk

```
 0
pashurst (2576)
6/25/2008 9:50:56 AM
```Bonsour=AE Hastey  avec ferveur  ;o))) vous nous disiez :=20

> I have X number of rows that show employee names and a column (A2)
> that lists the value of products sold by each employee and I want to
> pay them a percentage based upon value of sales. For example:
> If they sell up to =A31,000 worth of products, I will pay them 3% of =
the
> value.
> Between =A31,000 & =A32,000, I will pay them 5% of the value
> Over =A32,000, I will pay them 10% of the value
> The amount paid to them in commission will be show in column (A3).

=3DA2*CHOOSE(MATCH(A2,{0;1000;2000},1),3%,5%,10%)

HTH

--=20
--=20
@+
;o)))
```
 0
6/25/2008 9:51:03 AM
```Bill,

Pete

On Jun 25, 10:47=A0am, "Bill Ridgeway" <i...@1001solutions.co.uk> wrote:
> "Hastey" <darrenha...@sky.com> wrote in message
>
> I have X number of rows that show employee names and a column (A2)
> that lists the value of products sold by each employee and I want to
> pay them a percentage based upon value of sales. For example:
>
> If they sell up to =A31,000 worth of products, I will pay them 3% of the
> value.
>
> Between =A31,000 & =A32,000, I will pay them 5% of the value
>
> Over =A32,000, I will pay them 10% of the value
>
> The amount paid to them in commission will be show in column (A3).
>
> Many thanks
>
> D.Haste
>
> darren.ha...@ttt.co.uk
>
> You need to be aware of a perennial trap when calculating days difference=
..
> The answer lies in if you are counting gross or net days.
>
> In a simple example, if a person starts work on 1 January and ends work o=
n 2
> January, 2 days pay would be due. =A0Calculating the number of days pay b=
y
> taking the last day less the first day would result in an underpayment by
> one day. =A0It is, therefore, important when calculating gross days to ad=
d one
> day (i.e. =3Ddate2-date1+1)
>
> Bill Ridgeway
>
> Computer Solutions

```
 0
pashurst (2576)
6/25/2008 9:53:47 AM
```=MIN(A3,1000)*3%+(MIN(MAX(A3-1000,0),1000)*5%)+(MAX(A3-2000,0)*7%)

--
__________________________________
HTH

Bob

"Hastey" <darrenhaste@sky.com> wrote in message
I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to �1,000 worth of products, I will pay them 3% of the
value.

Between �1,000 & �2,000, I will pay them 5% of the value

Over �2,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

darren.haste@ttt.co.uk

```
 0
BobNGs (423)
6/25/2008 10:01:04 AM
```Yet another approach, just for the fun of it:

=A2*(0.03+(A2>1000)*0.02+(A2>2000)*0.05)

Best wishes Harald

"Hastey" <darrenhaste@sky.com> skrev i melding
I have X number of rows that show employee names and a column (A2)
that lists the value of products sold by each employee and I want to
pay them a percentage based upon value of sales. For example:

If they sell up to �1,000 worth of products, I will pay them 3% of the
value.

Between �1,000 & �2,000, I will pay them 5% of the value

Over �2,000, I will pay them 10% of the value

The amount paid to them in commission will be show in column (A3).

Many thanks

D.Haste

darren.haste@ttt.co.uk

```
 0
stf (176)
6/25/2008 10:04:18 AM
```Bonsour=AE Hastey =20

Yet another approach, just for flexibility :
define :
a range named : Amounts=20
0, 1000, 2000, 5000
and a range named : Bonus=20
2%, 3%, 5%, 10%

as many Amounts as Bonus

=3DA2*INDEX(Bonus,MATCH(A2,Amounts,1),1)

HTH

--=20
@+
;o)))
```
 0
6/25/2008 10:35:21 AM
```"Bob Phillips" <BobNGs@somewhere.com> wrote in message
news:O2Z%23qpq1IHA.5564@TK2MSFTNGP06.phx.gbl...
> =MIN(A3,1000)*3%+(MIN(MAX(A3-1000,0),1000)*5%)+(MAX(A3-2000,0)*7%)
*****************

This formula will give the correct answer.   The other formulas don't take
into account that the first 1000 of any amount will only be 3% commision.

>
> --
> __________________________________
> HTH
>
> Bob
>
> "Hastey" <darrenhaste@sky.com> wrote in message
> I have X number of rows that show employee names and a column (A2)
> that lists the value of products sold by each employee and I want to
> pay them a percentage based upon value of sales. For example:
>
> If they sell up to �1,000 worth of products, I will pay them 3% of the
> value.
>
> Between �1,000 & �2,000, I will pay them 5% of the value
>
> Over �2,000, I will pay them 10% of the value
>
> The amount paid to them in commission will be show in column (A3).
>
> Many thanks
>
> D.Haste
>
> darren.haste@ttt.co.uk
>

```
 0
sparky1 (3)
6/25/2008 7:54:59 PM
```The OP emailed me directly with further details - he gives 5% of the
sales value if sales are between 1000 and 2000, and 10% of the sales
value if over 2000, not on the sliding scale you imply.

Actually his sales values which trigger the different percentages are
a lot less than those quoted in the example, but I've just used them
here for continuity.

Pete

On Jun 25, 8:54=A0pm, "the Moderator" <sparky@no_spam_engineer.com>
wrote:
>
> This formula will give the correct answer. =A0 The other formulas don't t=
ake
> into account that the first 1000 of any amount will only be 3% commision.
>
```
 0
pashurst (2576)
6/25/2008 9:21:29 PM
```I'm glad about that, everyone else was going another route and I thought I
was off-key.

--
__________________________________
HTH

Bob

"the Moderator" <sparky@no_spam_engineer.com> wrote in message
news:XdmdnXcK6sUJP__VnZ2dnUVZ_gWdnZ2d@centurytel.net...
>
> "Bob Phillips" <BobNGs@somewhere.com> wrote in message
> news:O2Z%23qpq1IHA.5564@TK2MSFTNGP06.phx.gbl...
>> =MIN(A3,1000)*3%+(MIN(MAX(A3-1000,0),1000)*5%)+(MAX(A3-2000,0)*7%)
> *****************
>
> This formula will give the correct answer.   The other formulas don't take
> into account that the first 1000 of any amount will only be 3% commision.
>
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Hastey" <darrenhaste@sky.com> wrote in message
>> I have X number of rows that show employee names and a column (A2)
>> that lists the value of products sold by each employee and I want to
>> pay them a percentage based upon value of sales. For example:
>>
>> If they sell up to �1,000 worth of products, I will pay them 3% of the
>> value.
>>
>> Between �1,000 & �2,000, I will pay them 5% of the value
>>
>> Over �2,000, I will pay them 10% of the value
>>
>> The amount paid to them in commission will be show in column (A3).
>>
>> Many thanks
>>
>> D.Haste
>>
>> darren.haste@ttt.co.uk
>>
>
>

```
 0
BobNGs (423)
6/25/2008 10:26:02 PM

Similar Artilces:

determine mail flow problem!!!!!!!!
Help! :-( Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the outbound queue (which is set to always run delivery). Messages stay in there from 10 to 120 minutes before being sent to our gateway MTA and then sent off site (to hotmail.com for example). However when I telnet from a backend server to the gateway over port 25 and send a test message it goes right away. I can't for the life of me figure out why. One example when viewed in message tracking history says: 10:16am - smtp: store driver, message submitted from store 10:16am - smtp: message submitted to ...

Determine columns used
I have 100+ spreadsheets which i have to edit into a certain format. The spreadsheets currently have 100 columns (from 1-100). From there i have to reduce the number of columns set to 8 columns. E.g: 1 2 3 4 5 6 7 8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards, it goes to the 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 How do i do that without altering the order of the value of the cell? Let me make sure we understand the question. You have ...

how to determine the size of the sheet
Hi I had a collegue who told me once a way to dtermine the number of the rows in a sheet of the workbook I am working at. It is known that when you open a new workbook each sheet has maybe 65365 or something like that rows. MY QUESTION IS: HOW TO MAKE THE SHEET 1000 ROWS OR WHATEVER NUMBERS OF ROWS I ONLY NEED? Thanks in Advance, Ahmed Hi Ahmed The number of rows and columns are fixed. Your workaround would be to hide the ones you consider unneeded. HTH. Best wishes Harald "Ahmed SHEBL" <ahmad.shebl@hotmail.com> skrev i melding news:%231uFO3IcHHA.4720@TK2MSFTNGP0...

Dynamically determining when a month ends
Hello all, Ok, I have a simple excel spreadsheet, where I keep track of my spending on one tab, my income in another and my summary in a thrid. My problem happens when I try to dynamically calc. avg's for months in the summary page. For instance, if I want to see how much money I spent on average per month, or even per day, I draw from the data entered into the spending page. Now, the spending page is set up with the following columns: Date:: Description:: Amount The problem with this is that I can't predefine a max row # for each month, since I might have 80 entries in spending...

How do I determine
I have X number of rows that show employee names and a column (A2) that lists the value of products sold by each employee and I want to pay them a percentage based upon value of sales. For example: If they sell up to =A31,000 worth of products, I will pay them 3% of the value. Between =A31,000 & =A32,000, I will pay them 5% of the value Over =A32,000, I will pay them 10% of the value The amount paid to them in commission will be show in column (A3). Many thanks D.Haste darren.haste@ttt.co.uk "Hastey" <darrenhaste@sky.com> wrote in message news:6a0a4fa9-adcd-484a-80...

I'm trying to scan a list of servers for a specific file (say for example \\<servername>\C\$\MyApp\Test.ini\which may exist on some servers but not on others. In addition, I may not have have access to all servers that I'm checking. When using any commands in Powershell (1.0) that attempt to get the file (such as Get-Item) PowerShell returns the same "Cannot find path '<filename>' because it does not exist.)" error message in the following two scenarions: 1. I have access to the remote filesystem and the file does not exist (expected beha...

Determining A Rating
Hello, Suppose I have the following table: A B C 1 3 <3.80 2 3- 3.80 3.99 3 4+ 4.00 4.19 4 4 4.20 4.79 5 4- 4.80 4.99 6 5+ 5.00 5.19 7 5 5.20 5.79 8 5- 5.80 5.99 9 6+ 6.00 6.19 10 6 6.20 6.79 11 6- 6.80 6.99 12 7 7.50 7.99 13 8 8.50 8.99 14 15 4.91 16 4- Where A is a rating/score B is the low value range C is the high value range If I have a value in A15 of 4.91 (which is in the range of 4.80 and 4.99), I would like to return a rating in A16 of 4-. I've tried using a series of IF statements, ...

How do I determine the size of an excel worksheet?
How do I determine the size of an individual worksheet in an Office Excel 2003 file? The Properties featue only tells me the size of the whole file. Hi not directly supported by Excel. One workaround: Export this sheet to a separate workbook (with only this sheet) and measure its size -- Regards Frank Kabel Frankfurt, Germany "rajivsahib" <rajivsahib@discussions.microsoft.com> schrieb im Newsbeitrag news:7031AE58-EB7A-44CD-9AF5-940BD47DAA8B@microsoft.com... > How do I determine the size of an individual worksheet in an Office Excel > 2003 file? The Properties featue o...

Determine if a sheet is protected?
How can I determine if a sheet is protected or not? thank u Try activesheet.protectcontents HTH Bob "Robert Crandal" <nobody@gmail.com> wrote in message news:h865n.9557\$Ft3.8620@newsfe04.iad... > How can I determine if a sheet is protected or not? > > thank u > > Sub test() MsgBox ActiveSheet.ProtectionMode 'returns true or false End Sub Gord Dibben MS Excel MVP On Mon, 18 Jan 2010 16:28:18 -0700, "Robert Crandal" <nobody@gmail.com> wrote: >How can I determine if a sheet is protected o...

Determining OS?
Is there a method to determine the operating system (98, XP, ME, etc) that a program is running under? Thanks, -- Knute Johnson email s/nospam/knute/ Molon labe... >Is there a method to determine the operating system (98, XP, ME, etc) >that a program is running under? See the GetVersionEx API. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq David Lowndes wrote: >>Is there a method to determine the operating system (98, XP, ME, etc) >>that a program is running under? > > > See the GetVersionEx API. > > Dave Thanks. -- Knute Johnson email s/n...

Other field Determination
Imagine this situation...there are: Owners (tblOwners) Pets (tblPets, child records of tblOwners) Medicines (tblMeds, child records of tblPets) Owners can have multiple pets. Pets have multiple medicines. Easy so far. Here's the tricky part. The Pets can be of different species (cat, dog, bird, snake, etc). Depending on what the species is, I want to determine the other data that needs to be stored for the pet. So instead of just height, weight, age, as fields in the pet table, I want to see number of whiskers, declawed? if its a cat...wingspan, beaklength, feather color if its a bi...

Determining a Date
I have a column which is title, Expected Completition Date. Therefore multiple dates can be used. For example, July 1, July 17, August 31, August, 9, September 16, August 2, July 31, in that order. So for this project to be completed, it would be on September 16. I want the total field at the bottom to display the latest date of completion. =MAX(range) -- Carlos "Mark" <Mark@discussions.microsoft.com> wrote in message news:E0D47212-DCFE-4C47-84AB-708C4838E81E@microsoft.com... >I have a column which is title, Expected Completition Date. Therefore > multiple da...

How to determine Resources for a ServiceAppointment?
I am creating a custom view to list resources for each serviceappointment. Here is the basic query I have used that seems to produce the correct results: SELECT * FROM serviceappointment JOIN ActivityParty ON (ServiceAppointment.ActivityId = ActivityParty.ActivityId) AND (ActivityParty.PartyObjectTypeCode = 112) JOIN systemuser ON (ActivityParty.OwningUser = systemuser.systemuserid) WHERE (serviceappointment.StateCode <> 2) My question is why ObjectTypeCode = 112. Isn't that for Incident, not for ServiceAppointment? Can anyone clarify for me please? Thanks, Richard ...

how to determine schemas for validation
I am trying to validate document parts of Office 2007 documents. The method I have tried using is to sort through each XML file to see which namespaces are used, load the corresponding schemas for those namespaces, then try to validate the document against that schema set. When it gets to more complicated documents, for example a Word document utilizing many features, several open office XML schemas might be used. When I try to load them all into a XmlSchemaSet, it won't compile because certain elements will have been declared more than once. For example, if I load the following schemas ...

determine what program is used to view html link in outlook message
where do I set up the program that will be used to view links in html outlook messages? Last I knew Outlook used the default browser on your system. "Allan J. Behr" <al@thebehrs.net> wrote in message news:FF55A15C-F12A-4646-9775-EAE0091D8A33@microsoft.com... > where do I set up the program that will be used to view links in html > outlook messages? ...

Help me determine what I whould buy.
I'm a DB programmer running Windows ME on my home system, while all my customers are running Windows XP. I want to be able to take screen shots of forms that I create. Latter I may want to produce DB diagrams, but that's not important now. If I need to upgrade my system to XP, I will. What version of Visio should I buy? I know how simple this is to someone knowing this product, but I'm not there yet, sorry. You need Visio Professional edition for Software and Database functionality. The latest version is Visio 2003, which only runs on Windows 2000 or Windows XP. If you de...

Vista UAC/Privilege determination
I have two intercommunicating processes. Process A sets a hook into process B (for purposes I can't talk about due to NDA issues). The problem I'm having is this: if I run A as an ordinary user, it can't set a hook in B if I run A as administrator, it can hook B, but when the hook runs in B, it can't PostMessage back to A. It appears that I have a situation where if A is an administrator, then B is running at a privilege level lower than A but higher than an ordinary user. So while I can hook it, I can't have it send back to me. But running as a limited user, I can&#...

How to determine the ranking positions?
Does anyone have any suggestions on how to determine the ranking positions? There is a list of numbers under column A, I would like to determine the ranking number starting from max to min. Does anyone have any suggestions? For example, 5 Rank (3) 3 should be returned under column B 4 Rank (4) 4 should be returned under column B 6 Rank (2) 2 should be returned under column B 8 Rank (1) 1 should be returned under column B EXCEL 2007. 1. In cells A2 to A5 enter:- 5 4 6 8 2. Give the above a Range Name of:- RangeName 3. In cell B2 type:- =RANK(A2,RankRange...

data determines which table to use
Access XP. I have a table that has the fields "Extension code" and "Description" and I have a form where an "Extension code" is going to be entered following this criteria: (a) If the extension code is known it will be entered by the user and the field "Description" is filled by the form (b) If the extension code is unknown a 000 will be entered by the user and the "Description" is filled by the user My problem is that the "extension code" 000 has a relationship of one-to-many while the rest of the extension codes are a one-to-...

How do I determine if I have Exchange?
"Home users typically do not have an Exchange Server e-mail account; instead they use a POP3 e-mail account with an Internet service provider (ISP) or use a web-based e-mail service, such as MSN Hotmail. If you use a POP3 e-mail account, your ISP can provide you with your specific account information. People without Exchange Server accounts cannot use the features in Outlook that require an Exchange Server." The above message appears in the HELP file, but I still need help determining if I do have an "Exchange Server"? Thank you. Did you purchase Exchange Server and ...

Determine current cursor appearance
I use docmd.hourglass true/false throughout my code to provide some feedback to the user when a process is running. Occassionally, I have nested processes that do this, so before I set it to false, I really need to check what its value was when it entered the subroutine. In Excel, I can use application.cursor to determine the current value of the cursor. Is there something similar in Access? Dale -- email address is invalid Please reply to newsgroup only. hi Dale, Dale Fye wrote: > In Excel, I can use application.cursor to determine the current > value of the cursor. Is th...

Determining length of trend before significant retrace
In range of data I need to find jthe largest numerical movement in one direction without (x) retrace. (x in this case being a specified whole number) Please help with a formula or direct me to where I could find this information. Using Excel 2007 For example: in range of numerical data, the largest directional integer movement was 300 before the data moved 25 in reverse. Example 2: The largest directional integer movement was 224 before the data moved 30 in reverse. Assuming you have a column of numerical integer data in column B, In C2, enter =ABS(B2-B1) to calculate the magnitude ...