Ubuntu
openoffice.org package

creating a formula

Asked by Deb

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:
Last reply:
Revision history for this message
Aaron Hogan (aaron-hogan4452) said : 		#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 : 		#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 : 		#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
Best Tony Pursell (ajpursell) said : 		#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 : 		#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 : 		#6

Thanks Tony!!!