1

I'd like to learn if it is possible to make Excel function like some online calculators I've seen. For example:

enter image description here

The main point of difference is that these are designed so each 'cell' in the calculator is aware if it has had a value entered in to it - it will then conditionally calculate its value depending on its functional relationship with the others (or not).

This one uses a 'calculate' button, however there are examples with Excel-style live updating, in the case of not enough values yet and/or contradictory values they will usually state this, however, this isn't an essential feature (yet).

I have tried to implement similar in excel with conditionals but I find the cell logic is getting overly complex fast. Not helped in my case as the values in the system I'm solving for (not the one above) can be calculated from different subsets of inputs - you might say its 'over-defined'.

Does excel have an option or mode that can build this style of interface quickly and easily?

Alternatively, has anyone built similar and would be willing to share your tricks?

It would simply for my own productivity (and others, I hope!), I don't expect it to go online...

11
  • Something like support.office.com/en-us/article/… and support.office.com/en-us/article/… come to mind?
    – Arjan
    May 27, 2017 at 8:48
  • What is the formula for volume as a function of height ?? May 27, 2017 at 10:34
  • 1
    @Gary'sStudent, that is just an example, not what the question is about.
    – Arjan
    May 27, 2017 at 11:07
  • 1
    @Arjan, The accepted answer gave me the information I needed to produce my own calculator 🤗. May 27, 2017 at 21:47
  • 1
    Very well; I've reverted the change I made to the title. As an aside: no need for VBA if you're okay with using separate cells for the user input and the calculated results.
    – Arjan
    May 28, 2017 at 7:07

2 Answers 2

2

This can be done as follows in Excel Worksheet Using VB

We know Volume of a Cube = Length * Width * Height

Let us Make a Calculator For this :

Given any 3, 4th Value has to be found

Create Excel Worksheet

In A1, A2, A3 Type Length, Width, Height. Leave A4 Blank In A5 Type Volume

Now Create a Button using Tool Bar and Right Click the Button and Change Text To " CALCULATE " to give required name to the button.

Now Right Click the Button and Assign Macro to this button Creating On Button Click Command As follows :

Sub Button1_Click()

If Range("B1").Value = 0 
Then Range("B1").Value = Range("B5").Value / Range("B3").Value / Range("B2").Value

If Range("B2").Value = 0 
Then Range("B2").Value = Range("B5").Value / Range("B1").Value / Range("B3").Value

If Range("B3").Value = 0 
Then Range("B3").Value = Range("B5").Value / Range("B2").Value / Range("B1").Value

If Range("B5").Value = 0 
Then Range("B5").Value = Range("B1").Value * Range("B2").Value * Range("B3").Value

End Sub
3
  • For Eg. Enter Lenth = 15 Width = 8 Height = 12 Now CLick Calculate to see the answer Volume = 1440 CUbic Units. We have used the formula Volume V=LengthWidthHeight Now Change any 3, 4th Would be found when you press the buttuon. May 27, 2017 at 10:47
  • Hrrrm, some of my values may well be '0' as inputted by the user, is there another way to test if the cell is empty? May 27, 2017 at 13:47
  • 1
    ah, found it "If IsEmpty(Range("B1").Value)" :D May 27, 2017 at 13:55
1

Doing this with worksheet cells can be a bit tricky, as you have found out.

But it's fairly easy to do this with VBA. The code can be in a Worksheet_Change event, which can be set up to fire whenever any one of the four cells is changed.

The logic then, is easy: if exactly three cells have a value, work out which cell is empty, perform the calculation and put the result into that cell.

2
  • Hello teylyn, thanks for the tip - I haven't looked in to VBA fully but it concerned me as a mac user that this was among the first links in a web-search! : answers.microsoft.com/en-us/msoffice/forum/… May 27, 2017 at 7:35
  • 1
    I don't use a Mac, but as an enthusiastic Excel user I would never want to. Mac Excel is roughly where Windows Excel was in version 2007, i.e. more than 10 years behind what Windows Excel can do. If you want to do serious work with Excel, get Parallels on your Mac and run Excel for Windows. I don't want to sound harsh, but Mac Excel is really, really far behind. It's nice for changing a few numbers, but not powerful enough for Excel power users.
    – teylyn
    May 27, 2017 at 7:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .