Trying to calculate fuel consumption rate for diesel engines

ctice

I am trying to copy an existing excel formula into smartsheet to calculate fuel consumption on our diesel generators to give us an approx time available on current fuel level. I have attached a word doc for comparison on both of them.

formula.docx

dojones

I don't see a TREND function in Smartsheet.

jmyzk_cloudsmart_jp

Hi @ctice

Since Smartsheet does not have the TREND function, I used simple mathematical calculations to replicate its functionality. If the two known GPH values and LOAD values are y1, y2 and x1, x2, you can solve the following equations to calculate a (slope) and b (intercept):

The TREND  function returns values along a linear trend. It fits a straight line (using the method of least squares) to the array's known_y's and known_x's. TREND returns the y-values along that line for the array of new_x's that you specify.

https://support.microsoft.com/en-us/office/trend-function-e2f135f0-8827-4096-9873-9a7cf7b51ef1

y1 = ax1 + b
y2 = ax2 + b

Step-by-Step Process:

1. Calculate the Slope (a):
   Subtract the two equations to eliminate b and solve for a:
   a = (y2 - y1) / (x2 - x1)
   This gives the rate of change in GPH per KW between the two known points.
2. Calculate the Intercept (b):
   Substitute a into one of the original equations (e.g., y1 = ax1 + b) to solve for b:
   b = y1 - a * x1
   This ensures the line passes through the given points.
3. Predict the Value for Any Load:
   With a (slope) and b (intercept) calculated, you can predict the GPH (y) for any input load (x) using the linear equation:
   y = a * x + b

Using INDEX MATCH in place of IFs

Your original Excel formula has many IFs to cope with various Load ranges.

=@IF(AND(2500>=C14,C14>=2250),TREND($C$44:$C$45,$B$44:$B$45,C14,TRUE),

IF(AND(2250>C14,C14>=2000),TREND($C$45:$C$46,$B$45:$B$46,C14,TRUE),

IF(AND(2000>C14,C14>=1875),TREND($C$46:$C$47,$B$46:$B$47,C14,TRUE),

IF(AND(1875>C14,C14>=1750),TREND($C$47:$C$48,$B$47:$B$48,C14,TRUE),

IF(AND(1750>C14,C14>=1500),TREND($C$48:$C$49,$B$48:$B$49,C14,TRUE),

IF(AND(1500>C14,C14>=1250),TREND($C$49:$C$50,$B$49:$B$50,C14,TRUE),

IF(AND(1250>C14,C14>=1000),TREND($C$50:$C$51,$B$50:$B$51,C14,TRUE),

IF(AND(1000>C14,C14>=750),TREND($C$51:$C$52,$B$51:$B$52,C14,TRUE),

IF(AND(750>C14,C14>=625),TREND($C$52:$C$53,$B$52:$B$53,C14,TRUE),

IF(AND(625>C14,C14>=500),TREND($C$53:$C$54,$B$53:$B$54,C14,TRUE),

IF(AND(500>C14,C14>=250),TREND($C$54:$C$55,$B$54:$B$55,C14,TRUE),

IF(250>C14,(31),0))))))))))))

You can use TEXT functions and INDEX MATCH to create a helper column, Lower Range, to simplify the formula;

=IF(CONTAINS(" - ", [Load Range (KW)]@row), VALUE(RIGHT([Load Range (KW)]@row, LEN([Load Range (KW)]@row) - FIND("-", [Load Range (KW)]@row) - 1)))

Purpose:
This formula extracts the lower bound of the Load Range (KW) from a range written in the format "Upper - Lower".
For example, from "2500 - 2250", it extracts 2250 as the lower bound.

Key Steps:
CONTAINS(" - ", [Load Range (KW)]@row):
Checks whether the Load Range (KW) contains the " - " separator. This ensures the formula processes only properly formatted ranges.

FIND("-", [Load Range (KW)]@row):
Finds the position of the "-" character in the string (e.g., "2500 - 2250").

LEN([Load Range (KW)]@row) - FIND("-", [Load Range (KW)]@row) - 1:
Determines the length of the portion of the string after the "-".

RIGHT(...):
Extracts the substring starting from the character after the "-".

VALUE(...):
Converts the extracted substring into a numeric value.

Use Lower Range as the MATCH value in INDEX (MATCH formula to get y1,y2, x1, x2

In the formula below, [100% Load GPH]$1 is the user input value we want to predict the GPH value.

The INDEX function gets the GPH value using the row number provided by the MATCH function. The MATCH function has the search_type, -1, returning the row number, searching the value from the bottom 20th row that exceeds the [100% Load GPH]$1 value.

search_type—[optional] The default is 1. The manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1).

-1: Finds the smallest value greater than or equal to search_value (requires that the range be sorted in descending order)

https://help.smartsheet.com/function/match

The [GPH Lower] adds 1 (+1) to get the value below the [GPH Upper].

In the [Load Upper], we change the range— The group of cells that you want to evaluate to $[Lower Range]$10:$[Lower Range]$20, leaving other formula structures the same.

[GPH Upper]=IF(ISNUMBER([100% Load GPH]1), INDEX([100% Load GPH]$10:[100% Load GPH]$20, MATCH([100% Load GPH]$1, $[Lower Range]$10:$[Lower Range]$20, -1)))

[GPH Lower]=IF(ISNUMBER([100% Load GPH]1), INDEX([100% Load GPH]$10:[100% Load GPH]$20, MATCH([100% Load GPH]$1, $[Lower Range]$10:$[Lower Range]$20, -1) + 1))

[Load Upper]=IF(ISNUMBER([100% Load GPH]1), INDEX($[Lower Range]$10:$[Lower Range]$20, MATCH([100% Load GPH]$1, $[Lower Range]$10:$[Lower Range]$20, -1)))

[Load Lower]=IF(ISNUMBER([100% Load GPH]1), INDEX($[Lower Range]$10:$[Lower Range]$20, MATCH([100% Load GPH]$1, $[Lower Range]$10:$[Lower Range]$20, -1) + 1))

Then, we can calculate the slope, intercept, and predicted value using the mathematical method explained at the top.

[Slop]=IF(ISNUMBER([100% Load GPH]1), ([100% Load GPH]2 - [100% Load GPH]3) / ([100% Load GPH]4 - [100% Load GPH]5))

[Intercept] =IF(ISNUMBER([100% Load GPH]1), [100% Load GPH]2 - [100% Load GPH]4 * [100% Load GPH]6)

[Predicted Value(TREND)]=IF(ISNUMBER([100% Load GPH]1), [100% Load GPH]1 * [100% Load GPH]6 + [100% Load GPH]7)

IF(ISNUMBER([100% Load GPH]1), prevent the error if the [100% Load GPH]1 is blank, not a number.

With the absolute reference ($) in the cell formulas, you can drag the formula in the [100% Load GPH] column to other columns.

Below is the published editable demo sheet. You can access it from the link at Smartsheet and change the values in the first row to test the formula.

If you want a copy of the sheet, please email me using the email address added to my profile.

https://app.smartsheet.com/b/publish?EQBCT=45efd022bb4346ff9d7ff1264d0edcfb (Link to the published sheet)