Help with expression

[ABBA]

Hi 

I am needing help with this

I got the expression correct but the results are not correct

IF
( AND
(OR
(
[Tank or Gauge Name]=“66018”,
[Tank or Gauge Name]=“Sales Tank #1”),

[Previous Day].[Total-Inches]< [Total-Inches]) ,
[Oil-Bbls]-[Previous Day].[Oil-Bbls],

"IF ([Tank or Gauge Name]=“11955”,[Previous Day].[Total-Inches]> [Total-Inches]) ,
[Previous Day].[Oil-Bbls]-[Oil-Bbls]")

@Steve 

@MultiTech 

[SkrOYC]

You may be trying to do this instead?:

IF(
  AND(
    OR(
      [Tank or Gauge Name]="66018",
      [Tank or Gauge Name]="Sales Tank #1"
    ),
    [Previous Day].[Total-Inches]< [Total-Inches]
  ),
  [Oil-Bbls]-[Previous Day].[Oil-Bbls],
  IF(
    [Tank or Gauge Name]="11955",
    [Previous Day].[Total-Inches]>[Total-Inches]
  ),
  [Previous Day].[Oil-Bbls]-[Oil-Bbls]
)

[ABBA]

Gives me an error

IF function is used incorrectly:three inputs should be provided --- (condition, if-result, else-result).

[SkrOYC]

IF(
  AND(
    OR(
      [Tank or Gauge Name]="66018",
      [Tank or Gauge Name]="Sales Tank #1"
    ),
    [Previous Day].[Total-Inches]< [Total-Inches]
  ),
  [Oil-Bbls]-[Previous Day].[Oil-Bbls],
  IF(
    [Tank or Gauge Name]="11955",
    [Previous Day].[Total-Inches]>[Total-Inches],
    [Previous Day].[Oil-Bbls]-[Oil-Bbls]
  )
)

Please notice that I still don't know what are you trying to do, I just fixed the obvious syntax mistakes

[ABBA]

Thank you so much

I tried and this is the error I get

IF function is used incorrectly:the second input (if-result) and third input(else-result) should have the same type.

I am trying to replicate the following

Tank or Gauge ID	Tank or Gauge Name	Type	Date	Total-Inches	Oil-Bbls	Notes	Production, Bbls	Transfer, Bbls	Sold, Bbls
MOSBY Dome Ellis-11955	11955	Oil	9/1/2022	74.25	123.83		0	0
MOSBY Dome Ellis-11955	11955	Oil	9/2/2022	50	100	Transferred to Mosby Sales Tank 1	0	23.83
MOSBY Dome Ellis-11955	11955	Oil	9/3/2022	22	36.69		5	0
MOSBY Dome Ellis-Sales Tank #1	Sales Tank #1	Oil	9/1/2022	25	65		3.45	0
MOSBY Dome Ellis-Sales Tank #1	Sales Tank #1	Oil	9/2/2022	30	88.83	Transferred from MOSBY Dome Ellis-11955	0	23.83
MOSBY Dome Ellis-Sales Tank #1	Sales Tank #1	Oil	9/3/2022	20	55.22	Sold	0		33.61
EAST Dome Ellis-Sales Tank #1-66018	66018	Oil	9/1/2022	25	65		3.45	0
EAST Dome Ellis-Sales Tank #1-66018	66018	Oil	9/2/2022	50	130	Transferred from EAST Dome Ellis-25293	0	65
EAST Dome Ellis-Sales Tank #1-66018	66018	Oil	9/3/2022	20	55.22	Sold	0		74.78

The above formula is for Transfer column

Tank or Gauge Name]="66018" and Tank or Gauge Name]="Sales Tank #1"both have same condition of [Total Inches] ....[Previous Day].[Total-Inches]< [Total-Inches]

Where as [Tank or Gauge Name]="11955", has opposite condition and hence opposite calculation

@SkrOYC 

Also, If the Notes column could say what it says in the column after performing the calculation by Guage, that would be like an Ultimate solution.

@Marc_Dillon 

@Suvrutt_Gurjar 

@dbaum 

@WillowMobileSys 

 

[WillowMobileSys]

IN the second IF...

     The "...[Total Inches] > [Total Inches]"  produces a Yes/No result

     BUT the "...[Oil Bbls] - [Oil Bbls]" produces a Decimal result

You cannot have different result types in an IF expression

[ABBA]

Thanks

How do we fix it?

[WillowMobileSys]

As others have subtly requested, you need to help us understand what you are trying to do with the expression.  It may also help to show, with an image, the column definition of where you are inserting this expression.

[ABBA]

@WillowMobileSys Thanks for the input. I am sorry If I was not elaborate in my explanation.

 I am wanting several if statements to execute one after the other for the TRANSFER column Expression in the table posted here

Tank or Gauge ID	Tank or Gauge Name	Type	Date	Total-Inches	Oil-Bbls	Notes	Production, Bbls	Transfer, Bbls	Sold, Bbls
MOSBY Dome Ellis-11955	11955	Oil	9/1/2022	74.25	123.83		0	0
MOSBY Dome Ellis-11955	11955	Oil	9/2/2022	50	100	Transfered to Mosby Sales Tank 1	0	23.83
MOSBY Dome Ellis-11955	11955	Oil	9/3/2022	22	36.69		5	0
MOSBY Dome Ellis-Sales Tank #1	Sales Tank #1	Oil	9/1/2022	25	65		3.45	0
MOSBY Dome Ellis-Sales Tank #1	Sales Tank #1	Oil	9/2/2022	30	88.83	Transfered from MOSBY Dome Ellis-11955	0	23.83
MOSBY Dome Ellis-Sales Tank #1	Sales Tank #1	Oil	9/3/2022	20	55.22	Sold	0		33.61
EAST Dome Ellis-Sales Tank #1-66018	66018	Oil	9/1/2022	25	65		3.45	0
EAST Dome Ellis-Sales Tank #1-66018	66018	Oil	9/2/2022	50	130	Transfered from EAST Dome Ellis-25293	0	65
EAST Dome Ellis-Sales Tank #1-66018	66018	Oil	9/3/2022	20	55.22	Sold	0		74.78

If ([Tank or Gauge Name]=“66018”, AND [Previous Day].[Total-Inches]< [Total-Inches] then [Oil-Bbls]-[Previous Day].[Oil-Bbls])

If([Tank or Gauge Name]=“Sales Tank #1”, AND [Previous Day].[Total-Inches]< [Total-Inches] then [Oil-Bbls]-[Previous Day].[Oil-Bbls])

If([Tank or Gauge Name]=“11955”, AND [Previous Day].[Total-Inches]> [Total-Inches] then [Oil-Bbls]-[Previous Day].[Oil-Bbls])

I have the formulas for SOLD column executing correctly. 

Thank you

[DroidTeo]

Can you give us some more details?

For example, what is expected and what is the current result..

[Aurelien]

Thank to @Suvrutt_Gurjar , I eventually found the original post of a duplicated one to which I responded earlier.

Here is my answer, in case that may help:

Hi @ABBA 

I re-indented the expression so that it's easier for me to read.

IF( 
  AND(
   OR(    
      [Tank or Gauge Name]=“66018”,
      [Tank or Gauge Name]=“Sales Tank #1”
    ),
    [Previous Day].[Total-Inches]< [Total-Inches]
  ),
  [Oil-Bbls]-[Previous Day].[Oil-Bbls],
  "IF ([Tank or Gauge Name]=“11955”,[Previous Day].[Total-Inches]> [Total-Inches]) ,[Previous Day].[Oil-Bbls]-[Oil-Bbls]"
)

 It seems correctly in terms of syntax, indeed, although the output is not matching between the result-true and result-false.

From my understanding:

- result-true is a numeric output

- result-false is a text-output

You may need to change one of these.

Did you want to make this?

IF( 
  AND(
   OR(    
      [Tank or Gauge Name]=“66018”,
      [Tank or Gauge Name]=“Sales Tank #1”
    ),
    [Previous Day].[Total-Inches]< [Total-Inches]
  ),
  [Oil-Bbls]-[Previous Day].[Oil-Bbls],
  IF (
    [Tank or Gauge Name]=“11955”,
    [Previous Day].[Total-Inches]> [Total-Inches] ,
    [Previous Day].[Oil-Bbls]-[Oil-Bbls]
  )
)

 If so, you may want to use IFS() expression instead of nested IF() expressions.

Here is my suggestion:

IFS( 
  AND(
   OR(    
      [Tank or Gauge Name]=“66018”,
      [Tank or Gauge Name]=“Sales Tank #1”
    ),
    [Previous Day].[Total-Inches]< [Total-Inches]
  ),
    [Oil-Bbls]-[Previous Day].[Oil-Bbls],
  [Tank or Gauge Name]=“11955”,
    [Previous Day].[Total-Inches]> [Total-Inches] ,
  TRUE,
    [Previous Day].[Oil-Bbls]-[Oil-Bbls]
)

For reference:

IFS() - AppSheet Help

[ABBA]

Thanks for the inputs and trying to solve this and help.

IFS( 
  AND(
   OR(    
      [Tank or Gauge Name]=“66018”,
      [Tank or Gauge Name]=“Sales Tank #1”
    ),
    [Previous Day].[Total-Inches]< [Total-Inches]
  ),
    [Oil-Bbls]-[Previous Day].[Oil-Bbls],
  [Tank or Gauge Name]=“11955”,
    [Previous Day].[Total-Inches]> [Total-Inches] ,
  TRUE,
    [Previous Day].[Oil-Bbls]-[Oil-Bbls]
)

In this solution, I am struggling as I am getting an error

IFS function is used incorrectly:Inputs to IFS() must be condition-value pairs. Input 4 is not a value of a consistent type.

 I am wanting several if statements o execute one after the other

If ([Tank or Gauge Name]=“66018”, AND [Previous Day].[Total-Inches]< [Total-Inches] then [Oil-Bbls]-[Previous Day].[Oil-Bbls])

If([Tank or Gauge Name]=“Sales Tank #1”, AND [Previous Day].[Total-Inches]< [Total-Inches] then [Oil-Bbls]-[Previous Day].[Oil-Bbls])

If([Tank or Gauge Name]=“11955”, AND [Previous Day].[Total-Inches]> [Total-Inches] then [Oil-Bbls]-[Previous Day].[Oil-Bbls])

[Aurelien · Accepted solution]

What about this ?

IFS( 
  AND(   
    [Tank or Gauge Name]="66018",
    [Previous Day].[Total-Inches]<[Total-Inches]
  ),
    [Oil-Bbls]-[Previous Day].[Oil-Bbls],
  AND(
    [Tank or Gauge Name]="Sales Tank #1",
    [Previous Day].[Total-Inches]<[Total-Inches]
  ),
    [Oil-Bbls]-[Previous Day].[Oil-Bbls],    
  AND(
    [Tank or Gauge Name]="11955",
    [Previous Day].[Total-Inches]>[Total-Inches]
  ),
    [Oil-Bbls]-[Previous Day].[Oil-Bbls]
)

[ABBA]

This is best!

Thank you!

I was wondering if there is a way to put comments in NOTES column after we execute the each of the subtraction statement.

[Tank or Gauge Name]="66018", [Notes]= "Transferred from EAST Dome Ellis-25293"

[Tank or Gauge Name]="Sales Tank #1", [Notes]= "Transferred from MOSBY Dome Ellis-11955"

[Tank or Gauge Name]=“11955"), [Notes]= "Transferred to Mosby Sales Tank 1"

[Aurelien]

Glad you made it.

You can use the same kind of expression in the column [Notes], but you will need to change the output of each IFS() part.

[ABBA]

Thanks 

I got it to work!

[WillowMobileSys]

---

@Aurelien wrote:

...[Previous Day].[Total-Inches]> [Total-Inches] ...

...[Previous Day].[Oil-Bbls]-[Oil-Bbls]...

---

Yes syntactly its correct.  But these two statements above are incompatible.  But we don't have enough details

My guess is the second IF is missing an AND to specify the criteria to perform the subtraction.  Something like the below - blue are the intended calcs the rest is criteria to decide when to do these specialized calcs:

IFS( 
    AND( 
        OR( 
           [Tank or Gauge Name]=“66018”, 
           [Tank or Gauge Name]=“Sales Tank #1” 
        ), [Previous Day].[Total-Inches] < [Total-Inches] 
    ), 
       [Oil-Bbls]-[Previous Day].[Oil-Bbls], 

    AND([Tank or Gauge Name]=“11955”, 
        [Previous Day].[Total-Inches] > [Total-Inches]
    ) 
       [Previous Day].[Oil-Bbls]-[Oil-Bbls],

    TRUE, ???? <<what is the default or general calculation to be used>>
)

 If this is more like the intended result, then the ???? portion needs to be filled in.