SharePoint List calculated column based on three different columns values

On one side, there are four dropdown columns (Scope, Time, Resources and Budget) that can contain only one the following values: Green, Yellow and Red.

On the other side, I want to make "Project Health" a calculated column with those same options (green, yellow and red) based on the 4 columns above.

The potential cases would be the following:

  1. If one of the four columns is RED, "Project Health" is RED
  2. If one of the four columns is YELLOW, "Project Health" is YELLOW
  3. If all four columns are GREEN, "Project Health" is GREEN.

I am using SharePoint 365.

sharepoint foundation – find text in calculated field

All,

i am trying to extract part of a string in a calculated field.
The calculated field is looking in the title field, of an item in the library, for the word “DIS”.
The formula I am using is the following:
=IF(ISNUMBER(FIND("DIS",Title,10)),MID(Title,INT(FIND("DIS"),Title,10)),13),"")

When it finds the word “DIS” I want the code to extract 13 characters. This code is working fine until the title consists of more than one instance of “DIS”. For example when it runs into the word “DISTANCE”.
I tried to get the last instance using RIGHT()
function in the formula because the characters I am looking for are always somewhere at the end of the title. I cant get this to work.
Can anyone help with that?

list – Calculated column based on what’s in field of another column in same row

I’m wondering if there’s a way to create a calculated column based on what’s in another column? For example, something like the pseudocode below:

If (column 1) is in ("item 1", "item 2", "item 3") then "category 1" else if (column 2) is in ...

I’ve looked at using something similar to =IF(ISNUMBER(FIND("v",(Column1))), "OK", "Not OK") but that doesn’t quite get me there.

sharepoint online – Calculated column with IF(AND(OR statement to derive a value

I would like to use an “=IF(and(or” function (or a function that works) to obtain an answer of 1 or -100 within a SharePoint list. My formula works in Excel but not in SharePoint.

=IF(OR(AND(Q10=”NO”,P10<=0.1),AND(Q10=”YES”,P10<=0.2)),1,-100)

Discount = 0.2
LVR<70% = yes
(RESULT WILL EQUAL 1)
If LVR is <70%, then the maximum discount can be up to 0.2 (ranging in value from 0.01 through to 0.2 with intervals of 0.01)

Discount = 0.1
LVR<70% = no
(RESULT WILL EQUAL -100)
If LVR is NOT <70%, then the maximum discount can be up to 0.1 (ranging in value from 0.01 through to 0.1 with intervals of 0.01)
This works in excel.

When I move it over to the calculated column in SharePoint
=IF(OR(AND((LVR<70%)=”NO”,(Proposed % discount)<=0.1),AND((LVR<70%)=”YES”,(Proposed % discount)<=0.2)),1,-100) SharePoint accepts it but doesn’t calculate the correct answer. (always gives -100 as the answer)

the calculated column is “The data type returned from this formula is: Single line of text”

Total Sum of two difference percentages calculated

Hope you are O.K.
I want formula in one cell.

100,000 is in Column A, Row 1

100,000 * 0.15% = 150 * 13% = 169.5

I need result 169.5 in Column B, Row 1

Looking forward to receive Formula soon.
Thanks & Regards.

c# – Why my calculated value is far different from actual value?

I using charactercontroller and not using a rigidbody so I writing a script for downward velocity which is velocity=-9.81*(time) and I declare my initial velocity as 12. Supposing from this calculation, the max height it can reach from the equation is roughly 6.7891 unit on y axis. By using the formulae
distance travel=initial velocity*(time)-0.5g*(time)^2,differentiate this w.r.t time, getting time=u/g=12/9.81=1.223, then max distance travel=12*(1.223)-0.5*(-9.81)*(1.223)^2=7.339 but from the coordinate below, can clearly seen the max distance travel before falling down is 13.2803unit–0.5499unit=13.8302unit? This is far exceed calculated value, wonder what went wrong?

Initial coordinate of y on ground=-0.5499
enter image description here

approximate final coordinate of y=13.2803
enter image description here

using UnityEngine;

 public class NewPlayerMovement : MonoBehaviour
 {
public CharacterController Controller;
//speed of the character
public float Speed = 12f;
// earth gravitational value
public float Gravity = -9.81f;
Vector3 velocity;
//hit point of character
float hp = 90;

bool Grounded = true;

public void OnControllerColliderHit(ControllerColliderHit hit)
{
   
    Grounded = true;
    hp -= velocity.magnitude;
    velocity.y = 0;
    Debug.Log(hp);
}
public void OnControllerColliderExit(ControllerColliderHit hit)
{
    Grounded = false;

}

public void Checkstatus()

{if(Grounded==false)
    {
        velocity.y += 0.5f * Gravity * Time.deltaTime;
    }
}
void Update()
{
   
    float x = Input.GetAxis("Horizontal");
    float z = Input.GetAxis("Vertical");
    Vector3 move = transform.right * x + transform.forward * z;

    Controller.Move(move * Speed * Time.deltaTime);
    
    if (Input.GetKeyDown(KeyCode.Space)&&velocity.y==0)
    {
        velocity.y = 12;
        Grounded = false;
        
    }

//check if it grounded
    Checkstatus();
    Controller.Move( velocity * Time.deltaTime);
}

}

sharepoint online – Substract a calculated time from antoher calculated column

I have a SharePoint list in a site as below:

enter image description here

Where below are columns:

  • Wer: Person field
  • Zeit_an & Zeit_ab: Date-Field with time included
  • Pause: calculated Column which calculates if its Friday or not. If its Friday then Pause=0,00 if not its 0,30
  • T-Soll: Also calculated column which calculates if its Friday or not. If its Friday then its 4,30 if not its 8,30
  • T-Stunden is just de difference between Zeit_an and Zeit_ab.

Now my Problem is “Ges-St.”. Here it should just substract “Pause” from “T-Stunden”

Can you guys help me out here please?

Calculated column using 4 different columns to derive an “approve” or “Decline” answer

I would like to use an IF function to obtain an answer to “Approve” or “Decline” within a SharePoint list. My formula works in Excel but not in SharePoint.

I have 4 SharePoint columns:

column name = (Request Type), excel cell =G2 (to be “approved” the word “TD” must be chosen)

column name = (Position title), excel cell =F2 (to be “approved” the word “MEM” must be chosen)

column name = (% Discount), excel cell =J2 (to be “approved” value must be <=0.6)

column name = (Criteria Total), excel cell =S2 (to be “approved” value must be >=100)

My formula works in Excel but not in SharePoint.
Excel formula is: =IF(AND(G2="TD",F2="MEM",S2>=100,J2<0.7),"APPROVE","DECLINE")

When I enter it in SharePoint I change the cells to the column name and it always says “decline” when the answer should be “approve”

Can someone please help me?

sharepoint foundation – Updating calculated field formula via CSOM with non-english regional settings fails with “The formula contains a syntax error or is not supported.”

In our current project we programmatically provision site collections, site columns and content types using CSOM/PnP. During this provisioning process the initial locale id is switched from 1033 to 1031 (“German (Germany)”) for the web.

One of our columns is a calculated site column that’s being used in a content type which is being attached to a list. Provisioning / updating the site column formula fails if the locale id is set to 1031 although the exact same formula can be successfully inserted via web UI, so the question is: why does it fail when CSOM is used? (It also works using SSOM PowerShell on-premises, but we’re focusing on SPO here.)

We are aware of the localizing aspects of calculated column formulas, so we use semi-colons instead of commas as function parameter delimiters.

During our tests I tried to narrow down the problem. Updating the formula with a trivial one (like =1 or ="Hello world!" or =(Title)) always works, regardless of the web locale id. As soon as I use a slightly more complex one like =CONCATENATE(Title;" (Hello World!)") it fails for the 1031 locale id, which is unexpected. Localizing the function name to the german VERKETTEN also never works. (Note that this is only a simple example to demonstrate the problem, our actual formula is slightly more complex and uses other functions and fields.)

Here’s an overview on what I’m doing in our test scenario:

  1. We create the calculated column using AddFieldAsXml on the webs FieldCollection using the default locale id 1033 and the formula =CONCATENATE((Title)," (initial version)"). This works fine.
  2. We add the column to a newly created content type and add that content type to a newly created list. This works fine.
  3. We change the Web.RegionalSettings.LocaleId to 1031. No problem here.
  4. We reload the calculated column from the Web.Fields collection and update the column formula to =CONCATENATE((Title);" (second version)") using FieldCalculated.UpdateAndPushChanges(true). This fails: The formula contains a syntax error or is not supported.
  5. We try the same operation using the calculated column from the List.Fields collection, again updating the the column formula to =CONCATENATE((Title);" (second version)"). This also fails: The formula contains a syntax error or is not supported.
  6. We open the web UI to modify the site column properties and insert the calculated formula =CONCATENATE((Title);" (second version)") there. This works fine.

I am stumped why the same formula works using the web UI but not via CSOM. What am I missing? Did we find a SharePoint bug?

mysql – Looking to output a calculated field in a LibreOffice Base form into a database field

I have a libreoffice base form for invoicing. I’m using this alongside MySQL. After the user selects the products the customer has purchased, the sum of the prices is outputted to a calculated field called “Total.” I’m looking to be able to output this result from the calculated field, to another field in one of my tables instead. Here is the SQL command for my calculated field.SQL Command for calculated field

Any assistance will be greatly appreciated. Thank you.