Struggling with IFERROR, Conversions and general equating.

Hello on the spreadsheet posted you will see I have a cardio, resistance and bodyweight tables and a calculations page.

1.On the CARDIOEX table I would like it so that I could write in either of the GPS co-ordinates columns (Km or Mi) and have them automatically convert to the other so that I can have both bits of information. also in the time section whenever I enter a time (45 mins for example) it always tries to put it in a date and time format whatever I do.

2. I would then like it to convert the distance and time in to pace (time/distance) for both Km and miles in the respective boxes without div/0 errors in unfilled boxes.

I couldn't wrap my head around those so this one is truly defeating me.

3. I would like to log the amount of calories burned for particular exercises.

That requires Calories (Kcal) = METS x weight (kg) x time (hours)

a)METS - The respective exercises MET value can be found on the calculations sheet.

b) I would like my weight to be the weight that is logged for the date (or nearest to that date) that is logged on personal details sheet

Thanks.

C) some METS values work purely on time some on pace and time you will see this in the cardio table.


Please let me know if you need more info.

Where in the workbook do you want these things to appear? Can you please do a manual mock-up in the workbook, highlight where the mock-ups are and post it again?

1a) Excel can store either a number or a formula in a cell, but not both. Getting two cells to "mirror" each other where either cell can be used as input is not achievable with spreadsheet functions alone. It is relatively easy to do with a macro, but you must decide if you want to use a macro or limit yourself to spreadsheet functions. If you are willing to use a macro, we have lots of examples here and across the internet (put something like "have excel cells mirror each other" into your favorite internet search engine). Let us know how we can help.
1b) I don't understand exactly what trouble you are having with your time entry. If you try to enter something that Excel can recognize as hh:mm:ss, then it will automatically turn that number into a date/time serial number. To my knowledge, there is no good way to stop Excel from doing this. If you want a cell to contain the number 45, it seems like it should be as simple as entering 45 into the cell, and Excel should not try to change that.
2) Once you figure out the data entry parts of step 1, step 2 seems like simple division functions. To avoid the Div/0 errors, simply include those divisions inside of an IF() function of some kind =IF(inputcell=0,"",divisionformula)
3) Also looks like a simple multiplication formula. The METS looks like it would be a lookup function of its own (VLOOKUP() help file: https://support.office.com/en-us/art...8-93a18ad188a1 ). The weight might also be a lookup formula, it's not clear to me. You appear to want to enter your time in minutes, so the time part will need to be converted from minutes to hours (the CONVERT() function may be useful here: https://support.office.com/en-us/art...d-666c810f9af2 ).