Function VBA to round down nearest half dollar

[MrByte]

I have these functions that I have created. After searching here and there for ways to accomplish what I want I have not found any solution. So it's up to MrExcels forum to the rescue

I want to have SECONDPRIZE and THIRDPRIZE functions to round down to the nearest half dollar ($.50). i.e. $12.45 becomes $12.00, $12.62 becomes $12.50 and $133.32 becomes $133.00.

Here is the code that have started and accomplished so far for the functions. Any ideas or help on this would be appreciated!

Function PRIZEPOOL(curSales As Currency)
'calc the prize pool for the total sales at 50%
    
    PRIZEPOOL = curSales * 0.5
    
End Function


Function JACKPOT(curPrizePool As Currency, numWinners As Integer)
'calculate the jackpot prize amount at 62% of prize pool

    If numWinners < 1 Then
        Exit Function
    Else
    JACKPOT = (curPrizePool * 0.62) / numWinners
    End If
    
End Function


Function SECONDPRIZE(curPrizePool As Currency, numWinners As Integer, numJackPotWin As Integer)
'calculate the second prize amount at 10% with a
'cap of $555 if no jackpot winner and/or rounded down to nearest $.50

    If numJackPotWin > 0 Then
        SECONDPRIZE = Round(((curPrizePool * 0.1) / numWinners) * 2, 0) / 2
        Exit Function
    Else
        SECONDPRIZE = ((curPrizePool * 0.62) / numWinners) + _
            ((curPrizePool * 0.1) / numWinners)
    End If

End Function


Function THIRDPRIZE(curPrizePool As Currency, numWinners As Integer, numJackPotWin As Integer)
'calculates the thrid prize amount at 28% with and adds rolldown if
'no jackpot winner and/or rounded down to the nearest $.50

    If numJackPotWin > 0 Then
        THIRDPRIZE = Round(((curPrizePool * 0.28) / numWinners) * 2, 0) / 2
        Exit Function
    Else
        THIRDPRIZE = ((curPrizePool * 0.62) / numWinners) + _
            (currPrizePool * 0.28) / numWinners
    End If

End Function

 

[mikerickson]

To round down a value to the nearest half dollar, I'd use a formula like

=ROUNDDOWN(A1*2,0)/2

In VBA, =INT(SomeValue) + ((SomeValue*2) Mod 2) / 2

 

[MrByte]

Thanks Mik but after playing with the VBA you suggested it is not calculating correctly. Here is the code (from your idea) that I currently have in the function that I am creating.

SECONDPRIZE = (Int(curPrizePool) * 0.1 + ((curPrizePool * 0.1) * 2) Mod 2 / 2) / numWinners

The answer that I am getting is not resulting in a rounding number to either $xxx.50 or $xxx.00. I am getting $xxx.54 using the following numbers. $296,882.00 = curPrizePool, 224 = numWinners. Any ideas? I tried just breaking it down in a regular spreadsheet and I am not getting anywhere.

 

[shg]

Maybe ...

    SECONDPRIZE = WorksheetFunction.Floor(curPrizePool * 0.1 / numWinners, 0.5)

 

[MrByte]

Thanks that worked. But isn't that part of the Analysis Pack?

 

[shg]

Nope, just garden-variety.