SOLVED

FORMULAS IN EXCEL

Copper Contributor

I am trying to enter a formula onto sheet 1 of my spreadsheet so that if a certain measurement in inches is entered it will translate that into gallons based info located on sheet 3. Is there any such formula? 

 sheet 1sheet 1template on sheet 3template on sheet 3 

14 Replies
best response confirmed by MMONUS (Copper Contributor)
Solution

@MMONUS 

If you name the lookup table to be 'dataTable' then you can generate indices from the measurement in inches

= INDEX(dataTable, 
    1 + MOD(inches-1,24), 
    2 + 3*QUOTIENT(inches-1,24)
  )

If you are fortunate enough to be using Excel 365, it can be tidied up a bit to read

= LET(
    row,   1 + MOD(inches-1, 24),
    block, 1 + QUOTIENT(inches-1, 24),
    INDEX(dataTable, row, 3*block-1)
  )

@Peter Bartholomew 

 

Thank you for your help! Do I need to change the name of sheet 3 to "data table" or do I need to convert the info into a table somehow. I just typed that info in.

I am using microsoft 365 and I converted the info on my sheet 3 to a table and also made it all 1 column instead of 4 columns. So now, my inches of fuel are all in column A and the gallons they convert to are in column B

That would simplify the problem!
Well, it is not working... not surprising considering I am not an excel guru. Thanks for your help though!
Then let's go back to the beginning. Can you post a workbook rather than screenshots (you may have typed the lookup table but I am not keen to repeat the process)?
The most likely problem may be defining and referencing named ranges. In my preferred solution it would also help to be familiar with the LET function.

@MMONUS 

Sorry I jump in, just logic is not clear. 

If we have 30.25 inches shall we return value proportionally in between 2836 and 2967 gallons, or round inches as in the sample?

 

If round, when how, e.g. in the sample for 33.5 inches gallons are taken for 33 inches (default rounding gives 34 inches for 33.5).

 

Other words, bit more business logic.

@MMONUS 

In addition, this formula gives good enough approximation

image.png

This report was just given to me. in the future, i will be dealing only in whole numbers of inches...
that's awesome!

I was hoping to be able to enter the tank readings (in whole numbers) and have the gallons auto populate using a formula of some sort...

@Sergei Baklan 

I had also noticed that and thought of inserting INT.  In the event, I simply allowed INDEX to truncate the number of inches.  

I like the curve fit.  Now I could differentiate to determine the surface area and square-root for the radius.  Then draw the tank.

@MMONUS 

That is much easier.

= IF(TANK_READING<>"", 
     INDEX(Table2[GALLONS], TANK_READING),
     "")
You are my hero! Thank you so much!
1 best response

Accepted Solutions
best response confirmed by MMONUS (Copper Contributor)
Solution

@MMONUS 

If you name the lookup table to be 'dataTable' then you can generate indices from the measurement in inches

= INDEX(dataTable, 
    1 + MOD(inches-1,24), 
    2 + 3*QUOTIENT(inches-1,24)
  )

If you are fortunate enough to be using Excel 365, it can be tidied up a bit to read

= LET(
    row,   1 + MOD(inches-1, 24),
    block, 1 + QUOTIENT(inches-1, 24),
    INDEX(dataTable, row, 3*block-1)
  )

View solution in original post