How to calculate values between three cells using conditional calculation

[aaziz]

Hi. I am new to this forum and not very new to Excel (not in depth)

I have to perform a calculation for three cells by conditional calculation. so here is the scenario

Column Name: Total Area (Cell Address: A2)
Column Name: Remaining Area (Cell Address: B2)
Column Name: Allotted Area (Cell Address: C2)

As per snap shot
Capture.PNG

Now when I enter a digit in the A2 And B2, It subtract and put the result in C2. And if I enter a digit in the A2 And C2, It subtract and put the result in B2.

Can Anyone guide me how to perform this task.

[MrShorty]

I would abandon the idea exactly as you have it outlined. You are asking those three cells to serve as both input and processing/output, which, IMO, is a bad idea. It can be done, but it will require that you keep track of the cell dependencies and changes via VBA macros.

Here's how I would do it.
1) Designate 3 cells like you have done as input cells.
2) Add three more cells where you will output the calculations. These could easily be in the row immediately below the three you use for input.
3) The formula is fairly simple (assuming you will leave the unkown cell blank in the input). Here's how my spreadsheet would look:

Please Login or Register  to view this content.

Further calculations using these values can refer to row 3 instead of row 2.

I'm sure there are a number of people on the forum who can show how to use an event macro to "conserve" those three extra cells. If this were me, though, I'd avoid the macro route and use three extra cells like this to do it.

[Jarko28]

Hi aaziz,

In excel, a cell can either house a value or a formula, so if you want it to sometimes be based on a formula and other times be able to enter a value, you would need to use VBA...In the sheet module, you use place the following code:

Please Login or Register  to view this content.

I have attached a workbook with the example.

I am not sure if I understand correctly, but I think the below is the logic you want built in:

If total area is changed (A2), it resets that remaining area to the total area (B2) as no allotment has occurred yet, so (C2) is reset
If you choose to allocate some of the total area (C2), it reduces the value in the remaining area (B2)
If you choose the change the remaining balance (B2), it reduces the value in the allotted area (C2)

Let me know if this is what you were looking for.

Thanks.

[mrice]

Try pasting the above code into the Sheet1 Tab in the VBA editor (Alt F11)

Please Login or Register  to view this content.