3 Numbers Dependent Upon Each Other

ledjdt

New Member
Joined
Jun 30, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'm sure there is a simple fix to this problem but I am at a loss.
I have 3 numbers a quantity, total cost and item cost. I need to see each of them in a column each on its own row. I always know the quantity but sometimes I know the total cost or the item cost. I want to be able to put in one of these costs and have the other cost calculated. For example Q=2000,/TC=$4000 there for IC=$2 But if I only have the item cost then I want to find the total cost so Q=2000*IC=$2 therefore TC=$4000.
And I need to be able to change one of these numbers at any time and have it automatically change the other. I then use these numbers for different calculations. Any thoughts?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
One way to do this is with VBA but it's much simpler to do it with formulas. You need two cells to enter the data, and two more for the results.

$scratch.xlsm
ABCDE
1Quantity Enter Item Cost Enter Total Cost Calculated Item Cost Calculated Total Cost
2100 $ 1.00 $ 1.00 $ 100.00
3100 $ 100.00 $ 1.00 $ 100.00
4100
5100
Cost
Cell Formulas
RangeFormula
D2:D4D2=IF(OR($A2="",$B2&$C2=""),"",IF($C2="",$B2,$C2/$A2))
E2:E4E2=IF(OR($A2="",$B2&$C2=""),"",IF($B2="",$C2,$B2*$A2))
 
Upvote 0
I would have responded sooner but my internet was down for about an hour.

I took the VBA approach for an alternate solution.

Book2
ABC
11000Quanity
23Item Cost
33000Total Cost
4
Sheet1


Copy the following code to the sheet module of the sheet that you want to run it on:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
    Dim TargetValue As Variant
    Dim TargetRow   As Long
'
    If Intersect(Target, Range("A1:A3")) Is Nothing Then Exit Sub                   ' If the range that we are watching wasn't changed then Exit Sub
'
    Application.EnableEvents = False                                                ' Disable Events to prevent endless loop
'
    TargetRow = Target.Row
    TargetValue = Target.Value
'
    If IsNumeric(TargetValue) Then                                                  ' If the value entered is numeric then ...
        If Not Intersect(Target, Range("A1")) Is Nothing Then                       '   If A1 was changed then ...
            Target.Offset(2, 0).Value = TargetValue * Target.Offset(1, 0).Value     '       Set A3 = A1 * A2
        ElseIf Not Intersect(Target, Range("A2")) Is Nothing Then                   '   If A2 was changed then ...
            Target.Offset(1, 0).Value = TargetValue * Target.Offset(-1, 0).Value    '       Set A3 = A2 * A1
        ElseIf Not Intersect(Target, Range("A3")) Is Nothing Then                   '   If A3 was changed then ...
            Target.Offset(-1, 0).Value = TargetValue / Target.Offset(-2, 0).Value   '       Set A2 = A3 / A1
        End If
    End If
'
    Application.EnableEvents = True                                                 ' Enable Events
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top