Excel Formula Help Required.Solved/Closed

Question

Hello, 


I am looking for some kind of formula that can achieve the following.

i have a cell which allows a user to enter any number. i want excel to calculate from five predetermined numbers (2.4,3.0,3.6,4.2 & 4.8) which would be used.

If the user enters any of the five numbers , then excel returns the same number as the result. but also if the user enters anything other than any of the five numbers then it would pick the best of the five numbers to use.

ie if user enters 2.5 then excel should return 3.0 as result .. i always want the number rounded up.

again , if user enters 3.1 then excel should return 3.6 as result.

i think this is probably reasonably simple for excel to cope with , where i think it becomes a bit more tricky is ......i also want excel to calculate , if say the user enters 14.4 then would like excel to return 4.8 because its a multiple......but also want excel to offer the best use of multiples always rounding up....ie 14.5 would return 3.0 . 

any help much appreciated.

Configuration: Windows XP / Internet Explorer 8.0

venkat1926 · Answer

the operative cell where you want to enter the number is A1


in D1 to D5 enter those predetermined five numbers 


2.4
3
3.6
4.2
4.8

right click the sheet tab and click view code
in the window that comes up 
copy this EVENT CODE for this sheet 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Double, cfind As Range, r As Range, c As Range
Application.EnableEvents = False
On Error GoTo line1

If Target.Address <> "$A$1" Then goto line1
x = Target.Value
If Target.Value > Range("D1").End(xlDown) Then GoTo line1
Set r = Range("D1:D5")
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
For Each c In r
If c > x Then
Range("a1") = c
GoTo line1
End If
Next c

End If
line1:
Application.EnableEvents = True
End Sub


now type any number in A1 see what happens in A1
if the number is more than 4.8 nothing will happen

if the operative cell is not A1 you have change A1 in the sub to that cell address in all cases. for this purpose keep the cursor on the top of the event code and click control+H. in the "find what" type A1
under "replace with" type the operative cell address
ciick "replace all" in the bottom .

Excel Formula Help Required.

1 response

Similar discussions

Subscribe To Our Newsletter!