# creating a formula

Hi there, I am looking for a formula to calculate commission based on the total sales for the week, the percentages goes up from 3 to 5 % depending on the total sales for the week.

IE

1000 in sales = 3%
2000 = 4%
3000 and up 5%

thanks for the help,
:)

## Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Tony Pursell
Solved:
Last query:
 Revision history for this message Aaron Hogan (aaron-hogan4452) said on 2011-06-08: #1

You need to use an "if" formula.

Is 3% for total sells from \$1000 to \$1999, 4% from \$2000 to \$2999, and 5% for \$3000+?

 Revision history for this message Deb (daj2167) said on 2011-06-08: #2

Yes i figured it would be an "if" formula thanks and Yes thats right so it would be > or = to the sales.

 Revision history for this message Tony Pursell (ajpursell) said on 2011-06-08: #3

Hi Deb

If Sales are in A1 and you want the commission in B1, then what you want in B1 is

=IF(A1>2999,5,IF(A1>1999,4,IF(A1>999,3,0)))/100

and format B1 as % (there is a button for that on the toolbar.

Tony

PS If this answers your question, please mark it as Solved

 Revision history for this message Tony Pursell (ajpursell) said on 2011-06-08: #4

Or maybe

=IF(A1>=3000,5,IF(A1>=2000,4,IF(A1>=1000,3,0)))/100

Tony

 Revision history for this message Aaron Hogan (aaron-hogan4452) said on 2011-06-08: #5

A1 is the cell containing sales...

=IF(A1<=1000,A1*0.03,IF(A1>=2000,A1*0.04,IF(A1>=3000,A1*0.05)))

That formula will return the desired percentage of the total sales.

 Revision history for this message Deb (daj2167) said on 2011-06-08: #6

Thanks Tony!!!

To post a message you must log in.