Tag: Calculated

Calculated Column with IF ISBLANK, else if “ABC”, then return “XYX”

If [OH:Subsample ID] is blank, OR if it contains “ABC”, then the calculated field should return result “XYZ”… no idea how to put that into syntax…

I can get two separate lines, but can’t get them combined into one command line.. =IF([OH:Subsample ID]”Header Line”,”Audit”) =IF(ISBLANK([OH:Subsample ID]),”Audit”)

Calculated Column Not Displaying Anything

I have this really odd issue that works in 1 document library (top pic) and doesn’t for another (bottom pic).

I have 2 columns, Column 1 called ‘Published Date (Flow)’ that is of type string (single line) and Column 2 called ‘Valid Till’ that is of type calculated (date).

Based on the review period column (type number), I am supposed to calculate the valid till date from the published date.

enter image description here

enter image description here

The formula I am using for the calculated column is:

= [Published Date (Flow)] + ([Review Period (Months)]*30)

Thank you in advance for any help.

Calculated Date Column – Multiple IF and more

I’ve been trying to work this out for 2 days and I’m probably overcomplicating this but here’s what I’d like to do.

If the employee has been employed >= 2 years they only need to take certain classes annually, and one class every three years. If employed less than 2 years, one of the courses is annual instead.

Hired is a date column. Courses is a choice column of class options. Last Taken is the date the course was last taken. The calculated column Next Due, showing the next due date after inputting the class and date the employee took it. I did have minor success with IF(OR but not having success with the below. I tried to figure it out in Excel but I’m unable to build the formula there. Today is a column for the current date. I can either use a workflow to update daily or I can delete it after getting my formula right to trick SP (I’ve done both). 

=IF(Today>=DATE(YEAR([Hired])+2,MONTH([Hired]),DAY([Hired])), IF([Courses]="Class1",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))), IF([Courses]="Class2",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))), IF([Courses]="Class3",DATE(YEAR([Last Taken])+3,MONTH([Last Taken]),DAY([Last Taken]))))

The part below is if employed less than 2 years. It’s just part of the code I lifted out of what I’ve been working on. I know the syntax is incorrect but no matter what I do (if I join them, use OR, use AND) it’s just not working.

IF([Courses]="Class1",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))), IF([Courses]="Class2",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))), IF([Courses]="Class3",DATE(YEAR([Last Taken])+1,MONTH([Last Taken]),DAY([Last Taken]))))

I don’t know how to get it all to work. And I do use Notepad++ and check my brackets but it’s fruitless. Can someone help?

Thanks in advance!

Extract different text values from the title using calculated column

I am looking to find a way to extract text from the TITLE of an item when that text value could be different.

Example:
TITLE = N10 2019-2020 Kilo Project R Sec All Data
Calculated column called Section to pull out the section information (R Sec) is: 

=TRIM(MID(Title,SEARCH("sec",Title)-2,5))

This results in "Kilo Project; however the title of the section can be different in 4 ways
1. sec
2. national
3. set’s
4. SHQ

They all have to be entered into the same calculated column Section so I thought about making a nested IF and haven’t been successful in find a solution as everytime I try to I will get an error with the value (testing in excel).

Thanks in advance.

How is ViewsRecent in search results calculated?

I need to find the top 3 visited webs for the last 4 weeks and I need/want to do it via the REST search api. This is SharePoint 2013.

The value ViewsRecent returned by a search query seems to be what I’m looking for. According to this blog article the default timespan for the value of ViewsRecent is 2 weeks. The article also mentions a possibility to change the timespan that is used to calculate the value of ViewsRecent but the author does not (yet) say how. Has anyone done this before?

Calculation in Calculated Column varying based on null values?

Here’s one I’ve never dealt with before: I have calculated columns (in a SharePoint 2013 survey) that calculate values based on particular survey questions. One of those values, however, is an “N/A” value (currently set to the number zero). What I’m trying to do is this – I need to create an average of the NON-N/A answers. For example – I have 4 survey questions from which I extrapolate an average. If one of the of the answers is N/A, I need to get the average of the other three only. What should my calculated column formula be? I’ve never fathomed this one before.

Calculated Column Depending on Different Row Value

In our department, we can make offers with different packages. These offers are sometimes only one device and sometimes set. If the proposal (project number) package was submitted in a way, it should be written in the “Main Scope” column. If only one device has been submitted, the name of the device must be entered in the “Main Scope” column.

How do I write this condition?

enter image description here

Group by a calculated column and sort with swedish vocals

Environment: SharePoint 2019 – Modern Experience – Modern List

I’ve created a calculated column for grouping items alphabetically but when I group by this column the group are not sorting right the swedish vocals (ÅÄÖ they should be at the end).

When I sort without grouping is working fine.

Anyone with same issues?

Getting “#NAME” from SharePoint Survey calculated column [on hold]

In the results for a Survey done in SharePoint 2013 (survey list – type that I’m “inheriting”, if you know what I mean).

I noticed we’re getting “#NAME” in some of the areas we’re pulling down.

Here are the details of the survey and the field in question:

  • The columns in the survey where we’re getting #NAME are calculated columns based upon some of the answers in the other questions.
  • These answers fields are CHOICE columns (radio buttons) ranged from 1-9.

  • The calculation looks something like this:

    =AVERAGE(0+[Item A value],0+[Item B value],0+[Item C value],0+[Item D value],0+[Item E value],0+[Item F value],0+[Item G value])

Based on this information, what do you see that could be causing the error?

If Statement Giving Wrong Output Issue Calculated Column

I have this simple if statement where there are two columns. Column 1 = Score Column 2 = Test

This is the If Statement Code:

=IF([Score]>70,"A","F")

If the score is greater than 70 it should give an A in the Test Column. If it’s less than 70 it’s F.

The picture below is the issue. Am I doing something wrong here?

enter image description here