Help on Round Off value

[AutomationTester2011]

I have a bill amount = 140
Round off value = -40
Net Amount = 100

if bill amount is 160 then round off value is 40 and then net amount is 200

can anyone help in preparing a formula to auto calculate the round off value and net amount based on given bill amount.

Thanks in advance

[rajpes]

Code:

bill=140

Select Case bill
    Case "140": round_off=-40
    Case "160":  round_off=40
End Select

print "net is " & bill+round_off

[tdevick]

It looks like your rule is that your value is above n50, round up to the nearest 100, else round down to the nearest 100. For example, 140 rounds down to 100, 160 rounds up to 200. 240 rounds down to 200, 260 rounds up to 300. If that's true, you can do this:

Code:

Function RoundBill(billAmount)
  billHundreds=fix(billAmount/100)
  billRemaining=billAmount-(billHundreds*100)
  if billRemaining < 50 then
      RoundBill=(billHundreds)*100
  else
      RoundBill=(billHundreds+1)*100
  end if
End Function

billAmount=RoundBill(140)
billAmount=RoundBill(160)
billAmount=RoundBill(123)
billAmount=RoundBill(1780)

[rajpes]

Code:

Function f(billAmount)
   If mid(billAmount,2,1)<5 Then
       f=mid(billAmount,1,1)*100
   else
       f=(mid(billAmount,1,1)+1)*100
   End If
End Function
NetAmount=f(140)