 SOLVED

# FORMULAS IN EXCEL

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 1 template on sheet 3

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

# Re: FORMULAS IN EXCEL

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)
)``````

# Re: FORMULAS IN EXCEL

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.

# Re: FORMULAS IN EXCEL

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

# Re: FORMULAS IN EXCEL

That would simplify the problem!

# Re: FORMULAS IN EXCEL

Well, it is not working... not surprising considering I am not an excel guru. Thanks for your help though!

# Re: FORMULAS IN EXCEL

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.

# Re: FORMULAS IN EXCEL

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.

# Re: FORMULAS IN EXCEL

In addition, this formula gives good enough approximation # Re: FORMULAS IN EXCEL

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

# Re: FORMULAS IN EXCEL

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...

# Re: FORMULAS IN EXCEL

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.

# Re: FORMULAS IN EXCEL

That is much easier.

``````= IF(TANK_READING<>"",