Hi, Need Excel formula Format. For example if I enter a value in cell A1 ">45 etc. result show 1 in same cel A1 same <44 result show 0 in A1?

3 Answers

Relevance
  • 1 month ago

    Both Keerock and Garbo are correct in that you cannot enter a number in a single cell ANd change it.   Easier than VBA scripting, I recommend a 'helper column'  or helper cell. This is a cell or column where you enter your number and the HELPER column carries the formula and changes to 1 or 0 for your work, as Keerock has demonstrated. This is an accepted practice in Excel work and keeps you from having to create and maintain VBA scripts. 

  • keerok
    Lv 7
    1 month ago

    Are you asking about the IF function? See photo below. You can't put a value and a formula in the "same cell" though. 

    Attachment image
  • 1 month ago

    Your question is a bit unclear.  Your first test is ">45".  Your second test is "<44". The case where A1 would be 45 exactly is not addressed.

    Thus I infer that you actually mean ">=45" and "<45".

    Additionally, it seems you wish to replace the value entered in A1 with a new value based on the value entered... in the same cell, A1.

    There is no formula you can use for this as it would create a circular reference.

    Thus, you would have to use a VBA Worksheet_Change event handler.  Here is one example of how to do that.

    Copy this event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy')

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Target.Address(0, 0) = "A1" Then

        If Target.Value >= 45 Then

            Target.Value = 1

        ElseIf Target.Value < 45 Then

            Target.Value = 0

        End If

    End If

    Application.EnableEvents = True

    End Sub

    Select the worksheet containing the cell to modify and right click the sheet tab at the bottom.

    Select 'View Code'.

    Paste the event handler into the white editing area to the right (right click inside the area and 'Paste')

    Close the VBE (Red button - top right)

    Save the workbook as an Excel Macro-Enabled Workbook to retain this functionality in future openings of the workbook.

    Enter the value 20 in A1 and press ENTER.  A1 will display '0'.

    Enter the value 388 in A1 and press ENTER.  A1 will display '1'.

Still have questions? Get your answers by asking now.