Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Become a Premium Member and unlock a new, free course in leading technologies each month.

Solved

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

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

Would

Is that what you want to do.

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

Question has a verified solution.

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

Title | # Comments | Views | Activity |
---|---|---|---|

Excel shared spreadsheet | 12 | 38 | |

Why Won't My Excel Workbook Refresh The Data | 2 | 47 | |

Excel file size grew while there isn't any data in it. | 3 | 24 | |

Search functions & INDEX Match | 11 | 22 |

Course of the Month4 days, 13 hours left to enroll

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