Forum Discussion
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 suc...
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) )
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)
)
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.