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>

 

[dougdc3]

amazing, thank you -
the calculation works exactly as needed!
A follow up question, if I enter a number in F4, I have to click the Macros button and then RUN for it do the calculation.
Is there a way to auto-run the macro every time the number in F4 is changed or updated?
I am working off Microsoft Excel for Mac 2016, so not sure if this is a mac issue or something in all of excel.

 

[Caleeco]

amazing, thank you -
the calculation works exactly as needed!
A follow up question, if I enter a number in F4, I have to click the Macros button and then RUN for it do the calculation.
Is there a way to auto-run the macro every time the number in F4 is changed or updated?
I am working off Microsoft Excel for Mac 2016, so not sure if this is a mac issue or something in all of excel.

That's great! Thanks for letting me know!

Ah sorry, i had set it up as a sub for testing purposes. I can modify it so it auto calculates everytime a number is entered in Cell F4. Bare with me 2 minutes...

 

[Caleeco]

This code will automatically run, everytime cell F4 is changed. Add this code to the applicable sheet (Probably Sheet1)?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("F4")) Is Nothing Then
    
    Dim CreditLevel As Long
    Dim UserCredits As Long
    Dim Payout As Long
    Dim BonusLevel As Long
    Dim BasePayout As Long
    Dim NumPayouts As Long
    Dim NumBonus As Long
    Dim CreditPayout As Long
    
    '====== Variables for Payout Increments and Payout Size
    CreditPayout = 500
    CreditLevel = 40
    BonusLevel = 200
    BasePayout = 1000
    
    
    UserCredits = ActiveSheet.Range("F4").Value 'This is where the user can enter their credit amount
    
    If UserCredits < CreditLevel Then
        Payout = 0
        ActiveSheet.Range("F5") = "$" & Payout
    
    ElseIf UserCredits = CreditLevel Then
        Payout = BasePayout
        ActiveSheet.Range("F5") = "$" & Payout
    
    ElseIf UserCredits > CreditLevel Then
        NumPayouts = (Application.Floor(UserCredits, 40)) / CreditLevel
        NumPayouts = NumPayouts - 1
        
        NumBonus = (Application.Floor(UserCredits, 200)) / BonusLevel
        
        Payout = BasePayout + (NumPayouts * CreditPayout) + NumBonus * CreditPayout
        ActiveSheet.Range("F5") = "$" & Payout
    
    End If


End If
    
End Sub

 

[dougdc3]

Perfect, amazing!
Thank you!!!
I just realized that I can plug your formula into a different spreadsheet and it can calculate grant $$ without the need of the long table -
absolutely genius!

Many thanks!

 

[Caleeco]

Perfect, amazing!
Thank you!!!
I just realized that I can plug your formula into a different spreadsheet and it can calculate grant $$ without the need of the long table -
absolutely genius!

Many thanks!

That's great, glad to be of help!

Yeah, I tend to stay away from reading data... especially when you can set up a formula to calculate it on the fly instead!