Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel - Compute Third Field Given Two Others

Posted on 2016-08-18
4
Medium Priority
?
56 Views
Last Modified: 2016-08-22
I know how to do this programmatically but I wonder if I can do this using only formulas... it's not obvious to me.

Referring to the image below, I want to be able to calculate the field with no entry when the other two are filled in.
 
The overall formula is Cap Rate = Income / Price... so if someone enters income and price, I'll compute the cap rate, if they enter cap rate and price, I'll compute compute income and so on.
 
At this point, the only way I can see to do it is to have a second set of fields below the first that contain the formulas.  Any other thoughts or am I going to have to add a "Compute" button and a chunk of VBA code?

TIA
 
missing.png
0
Comment
Question by:Wayne Herbert
4 Comments
 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 332 total points
ID: 41761749
You could have a formula in each of the 3 cells (which will result in a circular reference), then the user will only have to enter 2 of the 3 values. But that will be a one time thing as the formula will disappear once a value is entered. You will need to do as you thought and use the 2nd set of fields, or VBA.
0
 
LVL 12

Assisted Solution

by:tel2
tel2 earned 332 total points
ID: 41761752
Assuming that people who aren't named "Wayne" can also participate in this...

Would hiding that 2nd set of fields, and putting them to the right of the visible 3, make them any more tolerable, Wayne (Herbert)?
0
 
LVL 24

Assisted Solution

by:yo_bee
yo_bee earned 332 total points
ID: 41761832
You can create a table with three headings Price, Income, Cap. With Price have the formula and as you tab down the formatting and formulas carry down.

Is that what you want to do.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1004 total points
ID: 41765555
Without VBA, you'll need to have a separate range to display the resulting calculated value.  There are two approaches:
1) have an identical set of cells ("fields" as you described them) with formulas designed to display the inputted and calculated values
2) have a single cell that dynamically labels the calculated field and provides the calculation.

One can add some functionality by both testing the input conditions to ensure that at least two values are input, AND using Conditional Formatting to show that the result is proper.

I've attached a workbook that demonstrates both examples.  In the first, if there are not two (2) values in the Input cells, then the bottom section will turn black and a message "Cannot Calculate" will appear. In the second, there is only one box that shows the value to be calculated and will also display "Cannot Calculate" if there are not two input values.

VBA would let you do all this with one set of Input/Result cells.

Regards,
Glenn
EE_Q_28964252.xlsx
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question