how to reverse the results of input ??

[davey_deee]

Sorry, that's probably not the best description in the title!

Screen Shot 2016-08-29 at 10.53.13.png

I've created the above workbook so that when I input weight in stones/lbs (C6/D6) the result is converted into KG (E6) and the same applies to height, from feet and inches into CM. The results are then transferred into J4 and J5 respectively using e.g. =E6 and these results are used to manipulate the rest of the workbook.

Is there a way that if I were to input the date in KG or CM into cell E6 or E10, then that could reverse calculate so that the correct weight/height appears in C6/D6 and C10/D10 in Stones and feet.

Also, at the moment, if I input the weight and height manually into E6 or E10 in KG or CM, then the formula I have used for the converstion from Stones and Feet is lost and replaced by the figure I have just input???

Thanks in advance!

[AliGW]

An image is impossible to work with! Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Bear in mind that you can only have either manual input or a formula in any one cell, not both. There may be a way to do this with a formula, though.

[davey_deee]

Thanks Ali,

I have attempted to upload a copy of the workbook. I think all of the information you need is in my previos post. Thanks for your answer re having only manual input OR formula in a cell, that makes sense now!

[AliGW]

I think I can see a possible way of getting round this, but first, could you please tell me what the reverse calculations would look like as a formula? Sorry - I'm not particularly good at weights and measures!

[davey_deee]

That's going to be difficult as I've worked the weight formula between 2 cells (stones and pounds). So if I were to input in kg, I wouldn't know how to formula the result across two cells again. if it helps there are 2.2046 lbs per kg and 14 lbs per stone haha

I have found this with regard to the same issue I have with the reversal of the height input, maybe it will help, Although I only need to the nearest inch, not 1/8 inch...


If you want FEET AND INCHES (which neither of the other solutions address), one way:

Assume that your cm values are in column A, cells A1...Axxx.

In B1, enter

=INT(ROUND(A1 / 2.54 *8, 0) / 96)

to get feet. In an adjacent column, enter

=ROUND((A1 / 2.54 - B1 * 12) * 8,0) / 8

to get inches to the nearest 1/8". Copy B1:C1 down as far as required.

[AliGW]

Here's the genesis of an idea - what do you think? The yellow cells become input only. Can you work with something like this? Sorry, I didn't wait for your conversion formulae!!!

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3			Input your data below						Male
4			Weight						Weight (kg)	94	kg	14st	12lbs
5			st	lbs	kg				Height (cm)	183	cm
6			14	12	94				Age (years)	36.75	years
7
8			Height						BMR Base Value	2019
9			feet	inches	cm
10			6	0	183				Activity Level Multiplier (Harris Benedict)
11								1	No exercise	2423	kcal
12			Activity Level (drop down)					2	Light exercise (1-3 days/week)	2776	kcal
13			3					3	Moderate exercise (3-5 days/week)	3129	kcal
14								4	Heavy exercise (6-7 days/week)	3483	kcal
15			Age					5	Very heavy exercise (2x per day)	3836	kcal
16			36.8
17									BMR maintanence value	3129	kcal
18

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	J	K	L	M	N
4	=IF(E6<>"",E6,ROUND((C6*14+D6)*0.4536,0))	kg	=INT((J4/0.4536)/14)&"st"	=ROUNDUP((((J4/0.4536)/14)-INT((J4/0.4536)/14))*14,0)&"lbs"
5	=IF(E10<>"",E10,ROUND(2.54*(C10*12+D10),0))	cm

Sheet: Sheet1

[davey_deee]

I like it! I think that would be much cleaner to have input only too. However the actual conversion figure you have displayed in the example L4 and M4 are redundant, so those columns are not necessary in the workbook. The formula I'm using to calculate calories works from kg only.

I'm guessing the same could be done with the height?? C10, D10 and E10 in your example.

[davey_deee]

I see you've already done it !

[AliGW]

I put L and M in there because I thought you wanted anyone using the form to see what the equivalent in 'old money' would be. Yes, the same can be done with height, but I think you may well be able to adapt what I've given you to do that bit yourself. If not, just shout!

[davey_deee]

It's perfect. Thanks so much!!

[AliGW]

Great - glad to hear it!

[DBY]

Just for further information take a look at the CONVERT function, you may find it useful. See link:

http://www.excelfunctions.net/Excel-...-Function.html

DBY