Calculating two directions

[Chamuit]

TIA.

I need to be able to calculate in two directions. Not sure how to describe otherwise.

If I enter the number in the $ column then it would calculate the % or if I entered a number in the % then it would calculate the other way and populate the $ column. I've attached a sample picture.

excel.jpg

[AliGW]

You’ll need VBA for this - shall I move your thread?

[MrShorty]

Spreadsheet formulas don't do this sort of "input, process, and output all in the same cell(s)" very well. If I were doing this using spreadsheet formulas/functions, I would designate a block of cells for the inputs, then have another block of cells that process and output the results. Usually, the output block has formulas similar to =IF(ISBLANK(A2),formula to calculate result,A2)

If you absolutely must have the input and output in the same cells, you will likely need to use VBA (or other programming language) to read the input data, determine which quantities need to be calculated, calculate those values, then write the results back into the spreadsheet.

We can help you with either approach you would like to take.

[Chamuit]

I don't know VBA. I'll leave this here.

Could I have the first column be a value (whether $ or %), then the second column be a pulldown menu of $ or %, the third column would show the reverse % or $?

Then, how could I sum the amounts both $ and % in separate columns?

[MrShorty]

I tend to be optimistic that most things are possible with enough time, effort, and ingenuity. So, I am optimistic that we could figure something out where you have a column for inputs, a column for "type of input", and a third column for outputs. Your screenshot in the OP does not say anything about how the various values/percents are intertwined. I expect that the hardest part of the problem is figuring out the algebra of the problem -- untangling the different quantities/percents in the different possible input combinations. This is more algebra than programming. Once the algebra is done, then it will be a matter of programming the resulting algebra expressions into appropriate decision making (IF()) functions to put it all together.

[Chamuit]

Here's my first run at it. I basically made two "sides" which allows for side by side comparison. I am thinking I need to lock the formulas so they don't get changed accidentally.