Tying to write a IF formula

IF "fuel Type " = Diesel , Multiple "Gallons Dispensed" x "Price Per Gallon",

IF "fuel Type " = Gas , Multiple "Gallons Dispensed" x "Price Per Gallon",

I would also want to change the Price per gallon at a certain date, When I get a fresh delivery of fuel, without changing all info in the sheet, only moving forward from that date

Find more posts tagged with

Accepted answers

All comments

Cassie L

Hi @CPSchlosser, here is a formula you can use to calculate the Price based on the Fuel Type and Gallons Dispensed:

=IF([Fuel Type]@row = "Gas", [Gallons Dispensed]@row * [Price per Gallon GAS]#, [Gallons Dispensed]@row * [Price Per Gallon DIESEL]#)

—> It is basically saying: If Fuel Type is Gas, then multiply Gallons Dispensed by Price per Gallon Gas - Delivery A, otherwise (if fuel type NOT Gas), then do the same but with Price per Gallon Diesel - Delivery A.

Note: you will need to set up your Sheet Summary with the prices per gallon for Gas and Diesel - see screenshot.

Using Sheet Summary, you can house the Price data for Diesel vs. Gas and that is what the formula references for the price.

To lock in the price for each delivery, I would recommend the following:

Set up an Archive / Repository Sheet where you can move the row to as a snapshot in time for the price of that delivery. In the screenshot, you can see the Archive Trigger column has a checkbox, and I have set up automation so that when that is checked, the row is moved to the Archive.
Before you update the Sheet Summary with the new price for each delivery, use the checkboxes to move the existing rows to your archive. This will capture the prices at that point in time, and you could even include a Modified Date or Created Date column to capture the date.

Paul Newcome

I like to track my variables when I can for auditing purposes, so I would actually have a second sheet that has three columns.

Date type called "Effective Date".

Text/number called "Fuel Type".

Text/number called "PPG".

You would put in the date a pricing is to become effective (this also allows you to forward plan without having to change formulas or anything else if you know in advance what prices are going to be), the fuel type, and the price per gallon.

In your working sheet, insert a date type column called "Effective Date" and the system generated Created (date) column. In this "Effective Date" column, you would use this column formula:

=MAX(COLLECT({Reference Sheet Effective Date}, {Reference Sheet Effective Date}, @cell <= Created@row))

Then you can either put this into another column in your working sheet called "PPG":

=INDEX(COLLECT({Reference Sheet PPG}, {Reference Sheet Fuel Type}, @cell = [Fuel Type]@row, {Reference Sheet Effective Date}, @cell = [Effective Date]@row), 1)

Your final price column would be your number of gallons times this "PPG" column, or you can skip the "PPG" column and use the above directly in your multiplication like so:

=INDEX(COLLECT({Reference Sheet PPG}, {Reference Sheet Fuel Type}, @cell = [Fuel Type]@row, {Reference Sheet Effective Date}, @cell = [Effective Date]@row), 1) * [Number of Gallons]@row

CPSchlosser

Thank you for you help on this.

Maybe you can answer me another question.

I create a report for a gris sheet, pull in all info I want. This includes Summary info. (trying to get a total of gallons of fuel ) which works and looks great,

However when I print the page with the summary on it, Summary info does not print. Why & how can i make it??

Paul Newcome

Printing the summary / grouped pieces of a report is not currently possible. Below is a link to a Product Idea to develop this very capability that you can add your vote to.

In the meantime, we may be able to use a separate sheet with formulas containing cross sheet references to get the data you want. Are you able to provide some screenshots for context?

https://community.smartsheet.com/discussion/92534/provide-users-with-the-ability-to-print-a-grouped-report

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.