Circular Excel Formula

0
votes

I need to make an excel spreadsheet that accepts user input in one of two columns on the sheet. Whichever column does not have the manual input needs to calculate its value. To illustrate:

enter image description here

In this picture, there is a relationship between the three columns. Total Price = (Price / Unit) * (Unit Count).

The users of my spreadsheet will do one of two things:

  1. They will manually enter a value for "Price / Unit." If this happens, the "Total Price" needs to calculate (Price / Unit * Unit Count).
  2. They will manually enter a value for "Total Price." If this happens, the "Price / Unit" needs to calculate (Total Price / Unit Count).

Is there a formula that will enable this? Maybe an "If-C1-is-a-formula" function?? I'm thinking this is impossible.

EDIT: Macros are a no-go for me. Sorry for not mentioning it sooner. I will be developing this with Apache POI through Java, which doesn't allow for the creation of macros.

1
excelexcel-formula
You totally need an event handler. macros would be easy. – bto.rdz
Excel 2013 has a new function called 'FormulaText' that would return the formula of the cell you reference. However, I agree with the others that this should be done with VBA code. – jgridley

1 Answers

3
votes

If macros are not an option, then you will need a slightly different setup, like so:

tigeravatar non-circular method

On the left, is what the user will fill in. They will enter the Unit Count and the Price and then in column C they will select a Price Type which is a validation drop-down list pointing to $F$1:$G$1 (Price / Unit, Total price). Once they have selected the price type, the table will automatically populate appropriately by using formulas.

In cell E2 and copied down (Unit Count):

=IF(A2="","",A2)

In cell F2 and copied down (Price / Unit):

=IF(COUNTBLANK(A2:C2)>0,"",IF(C2=F$1,B2,B2/A2))

In cell G2 and copied down (Total Price):

=IF(COUNTBLANK(A2:C2)>0,"",IF(C2=G$1,B2,B2*A2))

Then you can format columns F and G to desired (in this case, you would probably format them as currency or accounting).

I should also mention that the resulting table can be on a different sheet, you will just need to use a named range for your data validation drop down list (or hard code it if it's only two options).