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