1

I am working on a fuel issuance list in SharePoint online with 3 different vehicle engines.

Using the columns given below:

  • Requester Name: (Name Field)
  • Vehicle Location (Dropdown List)
  • Vehicle Registration (Dropdown List)
  • Previous-KM (Number field)
  • Current-KM (Number field)
  • Diff-KM (calculated field)
  • Vehicle-Model (Dropdown List)
  • K/Liter (Number field) Hidden
  • Fuel Pump (Calculated Field)

I want the Diff KM should be divided by a selected vehicle type on the drop-down list.

Vehicle Models & their Calculations:

TOYOTA PRADO 8Km/Liter,

TOYOTA LANDCRUISER LC200 7Km/Liter,

TOYOTA LANDCRUISER 7Km/Liter,

TOYOTA LANDCRUISER 79 7Km/Liter,

TOYOTA HILUX 9km/Liter,

TOYOTA HIACE Minibus 9Km/Liter

enter image description here

enter image description here

enter image description here

enter image description here

see error below after using the last formula enter image description here

enter image description here

I used this code on the JSON section but calculate for only one see screenshot below enter image description here

IF( OR([Vehicle Model]="TOYATA LANDCRUISER",[KM/Liter]="7",

IF(
    OR([Vehicle Model]="TOYOTA PRADO ",[KM/Liter]="8",
    IF(
        OR([Vehicle Model]="TOYOTA HIACE ",[KM/Liter]="9"),
        
        IF(
            OR([Vehicle Model]="TOYOTA HILUX" ,[KM/Liter]="9"),
            
            
        )
    )
)

)

4
  • urgently looking for a technical support for this post Feb 26, 2023 at 19:28
  • Where is the Vehicle Type in screenshot? Is this a choice column, if yes what are the choice options? What is the current formula you are using for Diff KM? Can you mention all the required conditions in your question? Feb 27, 2023 at 8:24
  • hello Ganesh, the vehicle type has been changed to "Vehicle Model" and its a choice column with a dropdown list. when you take a look at the post you will see it Feb 27, 2023 at 11:47
  • I have attached a screenshot of the Vehicle Model column, the entire list and the Diff-KM formula Feb 27, 2023 at 12:04

2 Answers 2

0

For example, you could create a calculated column named Diff KM then use below formula:

=IF([Vehicle type]="1","test1",IF([Vehicle type]="2","test2","test3"))

enter image description here

4
  • Thanks, Ganesh, I think you did not get my question clearly. let's provide all the necessary information. Feb 27, 2023 at 9:18
  • Thanks, Ganesh, for your prompt response. let's provide all the necessary information. the Diff KM column should be divided by the KM/Liter which is a hidden lookup column when the user chooses from the dropdown list VEHICLE MODEL and then provided the litres of fuel pump as per the screenshot above Feb 27, 2023 at 9:53
  • we can go to the chat room for a better explanation. at your local time always ready Feb 27, 2023 at 10:33
  • Hello, The formula that I need is that the “Diff-KM” reading should be divided by KM/Liter based on the “Vehicle model” chosen and to provide the number of liters of “Fuel Pump” • I have 6 different Vehicle models TOYOTA PRADO 8 KM/L, TOYOTA HIACE Minibus 9KM/L, TOYOTA LANDCRUISER LC200 7KM/L, TOYOTA LANDCRUISER 7KM/L, TOYOTA LANDCRUISER 79 7KM/L, TOYOTA HILUX 9KM/L, • I have A number column (KM/LITER ) 7,8,9 KM/Liter to be auto select from and should be a (hidden column) Please help Feb 27, 2023 at 18:47
0

As you have KM/Liter column in your list for each row (vehicle model), you can use below calculated formula for Diff-KM column:

=([Current KM]-[Previous KM])/[KM/Liter]

For safer side to avoid divided by 0 or any other error, you can use formula like:

=IF(OR(ISBLANK([KM/Liter]), [KM/Liter] = 0), 0, ([Current KM]-[Previous KM])/[KM/Liter])

In place of 2nd 0 in above formula, you can write any default value/expression as per your requirement.


Or use this better version:

=ROUND(IF(OR(ISBLANK([KM/Liter]),[KM/Liter]=0),0,([Current KM]-[Previous KM])/[KM/Liter]),2)

Output:

enter image description here

6
  • which Column should have the formula? there is only one column for (Vehicle Model) Feb 27, 2023 at 12:23
  • Formula should be for Diff-KM - calculated column. Feb 27, 2023 at 12:24
  • am having 0 reading from the Diff-KM column and 0 Fuel pump Feb 27, 2023 at 12:47
  • Does this work? =ROUND(IF(OR(ISBLANK([KM/Liter]),[KM/Liter]=0),[Current KM]-[Previous KM],([Current KM]-[Previous KM])/[KM/Liter]),2) Feb 27, 2023 at 13:00
  • it brings the Diff-KM but cannot divide the from the KM/Liter to get the Fuel-Pump value Feb 27, 2023 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.