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]”)
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]
)
Can you give us some more details?
For example, what is expected and what is the current result..
Gives me an error
IF function is used incorrectly:three inputs should be provided — (condition, if-result, else-result).
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
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
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.
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
Thanks
How do we fix it?
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:
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.
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.
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])
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]
)
@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
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”
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.
Thanks
I got it to work!