Simple 3 way conversion assistance needed

[A Prince]

I am trying to convert lbs to gallons to liters. I have all of the formulas and can do any of those things, but I would like to be able to input any one of the variables and have the other 2 computed. Right now I can input pounds and have the answer in gallons and liters. I can also input gallons and get liters but that is all i can do. Here is what I have done:

E10 = gallons (input number here) Once you do that then, E6=E10/6.7 (that gives you the weight of jet a fuel) and E8=E6*3.78 gives you the amount of liters based upon the weight of the gallons inputed in E10.

I would like to be able to put any of the variables (weight, gallons or liters) and get the other two answers and can not figure out how to do a round robin formulae.

I am sure that I am missing something pretty darn basic, but HELP plz....lol

 

[bensonsearch]

hmmm this can not really be done in a spreadsheet. have you thought of a userform or inserting controls?

only issue is this would create a circular referance in the formula's and excel will not like it.

 

[Peter_SSs]

Could you please confirm clearly the input cells (which is for gallons, litres, lbs) and the conversion formulas, they don't seem quite right to me.

So that I can check, what is the correct Lbs and Litres for 100 Gallons?

 

[Peter_SSs]

OK, with a bit of Googling I think I have the conversions roughly right.

As bensonsearch has indicated, with just E6, E8 and E10 as input and result cells, circular references would be a problem.

However, the following may be of use to you. If not exactly what/where you want hopefully a bit of tweaking should be all that is required.

I have used the yellow cells below as the input cell(s). The green cells contain the formulas shown and display the 3 results. If the yellow cells contain no numbers at all or more than one of the cells contains a number then the result cells show nothing, so you are required to enter a number in only one cell (see Note 1 below).

Excel Workbook
	B	C	D	E	F
5
6		100	Gallons	100
7
8			Litres	378
9
10			Lbs	676.2075
11
Conversion

Note 1
An added functionality would be that if you select one of the yellow cell (or a multi-cell selection contains any of the yellow cells) then the yellow cells will be cleared. So, after the calculation shown above if you want to do a different calculation, just select the appropriate yellow cell and the existing '100' in C6 would be cleared ready for your next input. To implment this ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try selecting one of the input cells and entering a value.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Const</SPAN> myCells <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C6,C8,C10"<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(myCells)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range(myCells).ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Note 2
There are a couple of proviso's with this added option ..

a) Macros must be enabled, and

b) It would fail if you have the Excel option to move the cursor after pressing Enter turned off.

 

[A Prince]

That worked exactly as I wanted it to work!! Not only does it work, but I can now see why it works!!

I will play with the "added functionality" a bit later.

Thanks a Bunch!!

 

[A Prince]

Hmmm...formulaes work just as advertised but I now have another problem. I imported the form into my iPad and iPhone using Documents to Go as i wish to use this as a down and dirty when I refuel at different airports. When I open the file in DoctoGo I get an error message that the file contains unsupported functions and therefore is read only. Then it list the cells with formulaes as being unsupported. And they are right, the form is definately unsupported! LOL

Any suggestions of other iPad and iPhone compatible programs that would allow me to use the formulaes?

 

[Peter_SSs]

Here's another option with a single 'value' input cell and a 'units' input cell with a Data Validation drop-down list of "Gallons", "Litres" and "Lbs". For this you wouldn't need any vba code.

Until both A2 and B2 contain data, the result cells remain blank but then display the required results.

If you are not familiar with Data Validation and can't figure out how to set that up, post back and say what version of Excel you are using.

You have probably already noticed, but be aware that we use different spelling of "litres".

Excel Workbook
	A	B	C	D	E	F
1	Input Value	Input Units
2	80.5	Litres
3
4
5
6				Gallons	21.49361
7
8				Litres	80.5
9
10				Lbs	144.0072
11
Conversion

#VALUE!

 

[Peter_SSs]

Hmmm...formulaes work just as advertised but I now have another problem. I imported the form into my iPad and iPhone using Documents to Go as i wish to use this as a down and dirty when I refuel at different airports. When I open the file in DoctoGo I get an error message that the file contains unsupported functions and therefore is read only. Then it list the cells with formulaes as being unsupported. And they are right, the form is definately unsupported! LOL

Any suggestions of other iPad and iPhone compatible programs that would allow me to use the formulaes?

Note that my previous post was not a responce to this post of yours.

I don't know about DocstoGo but we could try to work out what DocstoGo doesn't like about these formulas and see if thre is something else that still works.

Check the method from my last post but if that doesn't work, say which format looks most useful to you and also report how these formulas go in DocstoGo.

Excel Workbook
	A
1	5
2	1
3	2
4	3
5	3
Test

 

[A Prince]

You're right, data validation and I do not play well together. Even looking at a few googled pages only added to my confusion. LOL

You first example works great on my PC and it is exactly what I would like, but it does not work on my iphone or ipad unfortunately.

I am thinking now to make this work by having 3 sections. One section would input gallons and the resultant would be lbs and Litres. 2nd section would input lbs and output gallons and litres and of course the 3rd section would input Litres and output Gallons and Lbs. Not as pretty as your setup but might work with DocstoGo.

Just tested in DocstoGo and my way works, but yours is just so much cleaner.

For what it is worth I am using Office 2010 Excel version.

 

[Peter_SSs]

So what about these formulas in DocstoGo? What works and what doesn't?

.. report how these formulas go in DocstoGo.

Excel Workbook
	A
1	5
2	1
3	2
4	3
5	3
Test