creating a measurement conversion table

[dieseldaddy]

Hello. I am new to this forum and have searched for what I need but cannot find it.

I am looking to create an excel spreadsheet that simply converts similar units of measurement.
For example, 1000ul=1mL=0.001L and so on.
All calculations are simply multiply or divide by 100 or 1000s so this SHOULD be easy.
I tried VBA where if a certain cell (call it a1) was a number (1), then d1 (target 1,4?) would be 0.0001 and g1 (target 1,7?) would be 1000, etc.

My problem is that I think there are too many conditions. I need help creating a matrix where you can enter ANY number into ANY field...and the surrounding fields would calculate/convert properly. That's why I cant just use simple formulas in the spreadsheet.

Any help is greatly appreciated.

[rylo]

Hi

How many columns are used for each row, and what is the relationship between each of the cells in the row to say column A

So if A1 has 1, then what is in B1, C1....G1. Assuming that you are only multiplying / dividing by multiples of 10, this should be easy to determine.

rylo

[MrShorty]

All calculations are simply multiply or divide by 100 or 1000s so this SHOULD be easy.
I need help creating a matrix where you can enter ANY number into ANY field...and the surrounding fields would calculate/convert properly.

As you say, the calculations themselves are simple -- simply moving the decimal point around. What is difficult is that you are asking each cell to be both input and processing/output. In my experience, this is difficult in a spreadsheet. It will require a VBA procedure likely associated with an event (change and/or calculate).

If this were my spreadsheet, I would go with a different approach. Something like this:
1) Designate two cells as input cells -- one cell for the value and one for the prefix (k, m, c, etc.)
2) I would have a lookup table somewhere where I could lookup the "factor" for the prefix entered by the user.

Please Login or Register  to view this content.

Use one of the lookup functions http://office.microsoft.com/en-us/ex...670.aspx?CTT=1 to return the factor to convert the input value to the base unit
3) Then your row/table will just need functions to convert the base unit to each derivative unit

Please Login or Register  to view this content.

[dieseldaddy]

Thanks guys!
I'll try the lookup/factor approach first. I want them to be able to enter any field/measurement they have instead of defaulting them to use one or 2 specific ones.
I'll post back on how it goes.