Nov 02 2021 10:01 AM
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?
Nov 02 2021 01:20 PM
SolutionIf 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)
)
Nov 03 2021 06:34 AM
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.
Nov 03 2021 06:47 AM - edited Nov 03 2021 06:50 AM
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
Nov 03 2021 08:45 AM
Nov 03 2021 12:04 PM
Nov 03 2021 01:08 PM
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.
Nov 03 2021 01:17 PM
Nov 03 2021 02:25 PM
Nov 03 2021 02:34 PM
Nov 03 2021 03:39 PM - edited Nov 03 2021 03:50 PM
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.
Nov 03 2021 03:56 PM
Nov 02 2021 01:20 PM
SolutionIf 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)
)