help with formula

[dougdc3]

I am trying to create a tool in excel that will calculate a payment based on # of credits achieved based on the table below.
For example:
50 credits = $1,000
250 credits = $1,500
Any credit amount greater than or equal to 50, and less than 250 would be $1000.
Any credit amount greater than or equal to 250, and less than 500 would be $1,500.
Etc etc.

The excel sheet would start with a question asking a user to input the number of credits they have (cell labeled credits) and then would automatically provide the payment in a cell marked "payment"

Payment would be calculated based on the formulas above (referencing the table below)
The table is going to have numbers in the millions which is why I need a formula...


Credits Payment

50		$1000
100
150
200
250		$1500
300
350
400
450
500		$2000
550
600
650
700
750		$2500
800
850
900
950
1000		$3000

<tbody>
</tbody>

 

[Aladin Akyurek]

Try something like:

=LOOKUP(credits-to-evaluate,{0,50,250,500,750,1000},{0,1000,1500,2000,2500,3000})

 

[petereddy]

Try

=(ROUNDDOWN(B1/250,0)+2)*(B1>=50)*500

 

[Marcelo Branco]

Maybe

Formula
=IF(A2<50,0,1000+INT(A2/250)*500)

Where A2 houses the # of credits

M.

 

[Caleeco]

Hello,

So is the maximum payout $3000? or can the user accrue an infinite amount of credits?

If the user enters the numbers of credits they have, do you need to keep a record of what the answer was? or just display it in the "Payment" cell?

Also, if for example... the user entered 950 credits. Should the payout display $2500? or $2900?

 

[Caleeco]

Short bit of code to do what I think you want it to do. This just returns the value in a MsgBox to the user. Easy enough to change to write it to a "Payment" cell

Sub PayoutCalculator()


Dim UserCredits As Long
Dim Payout As Long


UserCredits = InputBox("How many credits do you have?", "Payout Calculator")


    If UserCredits < 50 Then
        Payout = 0
        MsgBox "Unfortunately you don't have enough points for your first payout!"
    Else
        Payout = (1000 + Int(UserCredits / 250) * 500)
        MsgBox "Your credit value has a payout amount of $" & Payout & "!"
    End If
    
End Sub

 

[dougdc3]

thank you - here is a link to the actual spreadsheet (I initially tried to include a picture but did not work)

https://www.dropbox.com/s/daa99lhi5jpqkh5/credit table 2016.xlsx?dl=0

 

[Aladin Akyurek]

thank you - here is a link to the actual spreadsheet (I initially tried to include a picture but did not work)

https://www.dropbox.com/s/daa99lhi5jpqkh5/credit table 2016.xlsx?dl=0

A scaled-down sample can be posted directly here using one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl.

 

[Caleeco]

thank you - here is a link to the actual spreadsheet (I initially tried to include a picture but did not work)

https://www.dropbox.com/s/daa99lhi5jpqkh5/credit table 2016.xlsx?dl=0

Took a quick look, you increment criteria seem to have changed. Also there seems to be an erroneous(?) $1000 grant at 200 credits. Or is that intentional?

From what I can tell.. the grant total is $1000, for the first 40 credits.

Then every 40 credits, add $500 to the grant total

40 credits - $1000
80 credits - $1500
120 credits - $2000
160 credits - $2500
200 credits - $3000

etc.

If this is the case, you do not need to have them listed in the spreadsheet as calculated values. You can simply have 2 cells. one to enter the credits, the second to work out the grant total.

 

[Caleeco]

If the assumptions in the above post are true. Please copy this code into the active worksheet VBA section. The section under where the variables are declared enables you to change the levels of payout and payout amounts on the fly.

Enter the amount of user credits in Cell F4 and it will display the payout amount in cell F5 when you hit enter!

Hope this helps

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Target.Worksheet.Range("F4")) Is Nothing Then


    Dim UserCredits As Long
    Dim Payout As Long
    Dim CreditIncrement As Integer
    Dim PayoutIncrement As Integer
    Dim InitialIncrement As Integer
    
    CreditIncrement = 40 ' This sets when the payouts increase, so every 40 credits in this example
    InitialIncrement = CreditIncrement 'Required for first if statement condition
    PayoutIncrement = 500 'How much each Payout increases
     
    UserCredits = ActiveSheet.Range("F4").Value 'Read the value in cell F4
    
        If UserCredits < InitialIncrement Then
            Payout = 0
            ActiveSheet.Range("F5") = "$" & Payout
        
        ElseIf UserCredits = InitialIncrement Then
            Payout = 1000
            ActiveSheet.Range("F5") = "$" & Payout
        
        Else
            Payout = (1000 + (Int(UserCredits / CreditIncrement) - 1) * PayoutIncrement)
            ActiveSheet.Range("F5") = "$" & Payout
        End If
End If


    
End Sub