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]

thank you!
I am not familiar with entering code in excel.

In answer to your question, yes, the table intentionally jumps to $1000 every 200 credits.
($500 every 40 credits, but $1000 when it hits 200, 400, 600, 800, 1000 credit milestones)

One user posted the following formula which worked up to 160 credits, however it stopped there and will likely not work if we want to carry out to 300,000 credits...

=IF(F82<40,0,IF(F82<80,$C$42,IF(F82<120,$C$82,IF(F82<160,$C$162,"value above equal 160")))
)

 

[Caleeco]

Ok Dougdc3, thanks for the clarification. That shouldnt be too hard to implement. Just need to add another condition to IF statement.

I'll have a look at it tomorrow, I'm sure we can get it working. Currently in bed it's 2am here! So speak to you tomorrow

 

[Aladin Akyurek]

thank you!
I am not familiar with entering code in excel.

In answer to your question, yes, the table intentionally jumps to $1000 every 200 credits.
($500 every 40 credits, but $1000 when it hits 200, 400, 600, 800, 1000 credit milestones)

One user posted the following formula which worked up to 160 credits, however it stopped there and will likely not work if we want to carry out to 300,000 credits...

=IF(F82<40,0,IF(F82<80,$C$42,IF(F82<120,$C$82,IF(F82<160,$C$162,"value above equal 160")))
)

Try to extend the following...

=LOOKUP(F82,{0,80,120,160},CHOOSE(MATCH(F82,{0,80,120,160},1),0,$C$42,$C$82,$C$162))

You can even create a name for the match bit...

Define Credits by means of Formulas | Name Manager as referring to:

={0,80,120,160}

The LOOKUP formula now becomes:

=LOOKUP(F82,Credits,CHOOSE(MATCH(F82,Credits,1),0,$C$42,$C$82,$C$162))

You can extend the set to match corresponding the results to suit.

 

[dougdc3]

thank you, (though I dont think I am completely following)

I defined "Credits" as cells A2:A256002
I am not sure which of your formulas I should copy into F162 (neither seemed to work)

 

[Aladin Akyurek]

thank you, (though I dont think I am completely following)

I defined "Credits" as cells A2:A256002
I am not sure which of your formulas I should copy into F162 (neither seemed to work)

You are not following the instructions. Credits stand for the set of values that F82 can take, that is, 0, 80, 120, 160, etc. Hence:

={0,80,120,160}

in the definition.

 

[dougdc3]

Thanks again for the quick response
I have never used the name function before and am obviously unfamiliar with the formula -

If possible could you provide some additional clarification/instructions?

(I.e. 'Paste this link in cell ___, select this range and name as ____, etc etc)

The clearer the better (sorry I'm still a newbee with excel)

 

[Aladin Akyurek]

Thanks again for the quick response
I have never used the name function before and am obviously unfamiliar with the formula -

If possible could you provide some additional clarification/instructions?

(I.e. 'Paste this link in cell ___, select this range and name as ____, etc etc)

The clearer the better (sorry I'm still a newbee with excel)

Activate the option Formulas | Name Manager from the ribbon.
Activate the New tab.
Enter Credits as name in the Name box.
Enter in the Refers to box:

={0,80,120,160}

Click OK.

Since you have a credit value in F82, in G82 enter:

=CHOOSE(MATCH(F82,Credits,1),0,$C$42,$C$82,$C$162)

Hope this helps.

 

[Caleeco]

^^ I think the method above is limited to the number to values you manually input. The example given only goes up to 160 credits.

The code below does what you need. I've tested it up to 1000 credits, but it can handle well over 300,000 credits as you need.

Sub CreditPayout()


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 Sub

 

[Aladin Akyurek]

^^ I think the method above is limited to the number to values you manually input. The example given only goes up to 160 credits.
[...]

It is limited to values the OP mentioned, not that the method is limited...

 

[Caleeco]

It is limited to values the OP mentioned, not that the method is limited...

That's true to some degree. Having to prescribe outputs is not the best way to solve all problems.

however, the OP mentioned he wanted to go upto 300,000 credits. So manual entry is not the approach to use in this instance.

Hence, i mention the code was "limited" to 160.. as in if they user enter 20,000 credits... no result will be returned(!)