Entering a % or Amount to calculate a new number

[talkmauro]

I am trying to figure out how to set up an excel calc to allow a user to enter a percentage or amount (see red text below) in adjacent cells to calculate a new value. If they enter a percent, it automatically calcs the amount and vis versa. It needs to be dynamic enough to go back and forth between the two values so if they try a percent but then want to switch to an amount they can do it without remembering to delete the value in the % or amount field.


[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Old[/TD]
[TD="width: 64"]%[/TD]
[TD="width: 64"]Amt[/TD]
[TD="width: 64"]New[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="class: xl63, align: right"]10%[/TD]
[TD="class: xl64, align: right"]100[/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD]or[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1000[/TD]
[TD="class: xl65, align: right"]10%[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1100[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nine Zero]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]%[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD]****[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


D2: =IF(ISBLANK(B2), A2+C2, A2*(1+B2))

 

[talkmauro]

Thank you this is good. I am not sure it solves all what I am trying to do. Does this allow them to go back and forth? Meaning if they have already entered 10% in B2 but they want to enter 150 into C2, can they just enter in 150 into C2 and it recalculates B2 to 15% and D2 to 1150?

 

[dreid1011]

Thank you this is good. I am not sure it solves all what I am trying to do. Does this allow them to go back and forth? Meaning if they have already entered 10% in B2 but they want to enter 150 into C2, can they just enter in 150 into C2 and it recalculates B2 to 15% and D2 to 1150?

No, you cannot have formulas and user entered values in the same cell. To do what you are after would require VBA. If VBA is an option, could you answer some questions?

-Is row 2 the only row users will be entering data? If not, what is the range that is used? If not a specific range, what column will have data in every row?

-Are columns B and C the only user inputs to be considered? With output in column D.

 

[talkmauro]

No, row 2 is one of many. Up to 600 different rows are needed, data should be in every row and in both B and C. Yes, we need to allow B and C available for them to change and output is column D.

 

[dreid1011]

No, row 2 is one of many. Up to 600 different rows are needed, data should be in every row and in both B and C. Yes, we need to allow B and C available for them to change and output is column D.

Assuming column A will be consistently full of data until the last row. Also what is the sheet name?

 

[dreid1011]

You can give this a try. Install it in the sheet module and change the highlighted "Sheet1" to the actual sheet name.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim lastRow As Long
Set ws = Worksheets("Sheet1")
Application.EnableEvents = False
lastRow = Range("A" & Rows.Count).End(xlUp).Row
If Not Intersect(Target, Range("B2:C" & lastRow)) Is Nothing And Target.Count = 1 Then
    If Target.Column = 2 Then
        If Target.Value <> "" Then
            Target.Offset(0, 2).Value = (1 + Target) * Target.Offset(0, -1).Value
            Target.Offset(0, 1).Value = Target * Target.Offset(0, -1).Value
        Else
            Target.Offset(0, 1).Value = ""
            Target.Offset(0, 2).Value = ""
        End If
    End If
    If Target.Column = 3 Then
        If Target.Value <> "" Then
            Target.Offset(0, 1).Value = Target + Target.Offset(0, -2).Value
            Target.Offset(0, -1).Value = Target / Target.Offset(0, -2).Value
        Else
            Target.Offset(0, 1).Value = ""
            Target.Offset(0, -1).Value = ""
        End If
    End If
End If
Application.EnableEvents = True
End Sub

 

[talkmauro]

Wow thank you. Does this automatically run once they try to enter data into either cell? How does it execute? I will let you know how it works.

 

[talkmauro]

WOW, that works great!! Thank you so much.

 

[dreid1011]

Once data is entered into either column B or C within the range of data, that code should fire. I also made it clear B, C and D if someone later deletes a value in B or C. If you don't want that feature, the code can be modified.