Formula depended from table with two diffrent units

[hungry4more]

Hi, i don't have alot of experience in excel but im trying to learn on my own using youtube and google.

Right now im trying to create a easy way to calculate the energy amount from the diffrent fuels. I have made a table with 40 diffrent fuels and the energy amount from all these. The thing thats makeing my calculation complicated is that not all fuels have the same unit. Some use volume (m3) and other weight (kg)(number 2).

Right now right now I have managed create a scrollable numbered list and connected the numbers to a formula box i.e. when you find the right fuel you wright in the number in the formula box and the value and name of the fuel follow (number 1). The problem now is how do I connect the amount to the forumla box?

In the formula box I two diffrent cells for the amount, one in m3 and the other in kg (number 2). How can i make a formula in (number 3) the formula box so excel understand what cell to multiply? How I didn't make it to hard to understand, follow the picture with the numbers. :)

[Kaper]

If there are only two types of units simple IF in your formula would do.
Unfortunately you attached (not really readible, because of lack of resolution) screenshot rather the spreadshhet, so I will not give you ready to use formula.

[hungry4more]

Kaper - Yes I've been looking on the IF formula on youtube but I still get confused somehow, im trying hard to learn because I'm starting to see all the advantages of excel :D. I'll add the file, the spreadsheet in question is the one called "energiberäkning".

[Kaper]

I think that proprer formula for L6 could read:

Please Login or Register  to view this content.

If you want to make it more developed you could use:

Please Login or Register  to view this content.

or may be:

Please Login or Register  to view this content.

In all examples above I used absolute addressing of B4:E52 range in sheet Tabell energislag - you can copy the cell with such formula to cells L9 L12 etc without editing it - G6, J6 etc will change to G9, J9 ... or G12, J12 ... but reference to B4:E52 will remain unchanged.

In the attached file I also added 1 column with units (just for information purposes - can be deleted without any side effects) with formula: =VLOOKUP(G6,'Tabell energislag'!$B$4:$E$52,3,FALSE)
I also made some conditional formatting of cells with kg/m3 (Polumns I and J) - see what hapens with cell interior colors if uou write value in proper cell or in wrong one. And then go to Conditional Formating and Rules Management to see rules I used. (Just to see more excel advantages :-D )