Excel Formula Help Required.

rooky - Oct 23, 2010 at 11:21 AM
 rooky - Oct 24, 2010 at 07:03 AM

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.

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 23, 2010 at 10:46 PM
the operative cell where you want to enter the number is A1

in D1 to D5 enter those predetermined five numbers


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
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 .
Yes , thanks for that , this is perfect.....but is there also any way for numbers bigger than 4.8 that excel can work out which of the 5 predetermined numbers would give the best multiple.