Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel - Compute Third Field Given Two Others
Posted on 2016-08-18
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4 Comments
Active today
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.
Active today
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)?
Would hiding that 2nd set of fields, and putting them to the right of the visible 3, make them any more tolerable, Wayne (Herbert)?
Active 1 day ago
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.
Is that what you want to do.
Active 2 days ago
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 17 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.