Formula Calculation

[dave]

Need formula that would calculate the following:
Scenerio: $69,000,000 in sales ( Cell A)
Commission( Cell B): .25% for 1st $50,000 / .30% for amount between $50,000-
$60,000 /.35% on amounts over $60,000

The value in cell B should be $ 186,500 ( $125,000 for 1st $50k, $30,000 for
next $10K, then $31,500 for the remainder $9K)

 

[fredg]

Need formula that would calculate the following:
Scenerio: $69,000,000 in sales ( Cell A)
Commission( Cell B): .25% for 1st $50,000 / .30% for amount between $50,000-
$60,000 /.35% on amounts over $60,000

The value in cell B should be $ 186,500 ( $125,000 for 1st $50k, $30,000 for
next $10K, then $31,500 for the remainder $9K)

Cell A? Cell B?
Sounds like an Excel problem.

You have posted this question to the wrong newsgroup.
The access in this groups title refers to Microsoft Access, a database
program.
Please repost to the correct newsgroup for the program you are using.
I would suggest you include your Windows and Office version number in
your message. It might make a difference.

 

[Linq Adams via AccessMonster.com]

If it is, in fact, I assume, for an Access database, let us know. At any rate,
you need to look back over your post and make some things clearer, either
here or elsewhere..

Paying $125,000 commission for $50,000 in sales doesn't really make sense.

And a commission of of .25% (1/4 of 1%) seems a little low. IS this really
what you meant, or did you mean 25% ?

 

[Linq Adams via AccessMonster.com]

In case someone finds this thread in a search and does need this for an
Access app, it something like this would do it:

This is assuming that the total sales is physically entered (if entered thru
code or code calculations, you'd need to call the AfterUpdate procedure)

Also assuming the commissions paid were 25%/30%/35%:

Private Sub TotalSales_AfterUpdate()
Dim Sales As Currency

If Not IsNull(Me.TotalSales) Then

Sales = Me.TotalSales

Select Case TotalSales

Case Is > 60000
Me.Commission = (Sales - 60000) * 0.35
Sales = 60000
Me.Commission = Me.Commission + (Sales - 50000) * 0.3
Sales = 50000
Me.Commission = Me.Commission + (50000) * 0.25

Case Is > 50000
Me.Commission = (Sales - 50000) * 0.3
Sales = 50000
Me.Commission = Me.Commission + (50000) * 0.25

Case Is <= 50000
Me.Commission = Sales * 0.25

End Select
End If

End Sub

 

[tina]

Paying $125,000 commission for $50,000 in sales doesn't really make sense.

no it doesn't, Linq, but i want that job! <g>

 

[Linq Adams via AccessMonster.com]

You and me both, Tina!

;0)>