# Calculated value formula

I need help with the formula for the following problem.

I have a list where you fill in a number of ordered items, and another column where you fill in how many of the items that are receieved.

Lets call them `[ordered]` and `[receieved]`

I aldo have a Status column with 3 choices. The choices are:

• "Waiting for delivery"
• "Partly delivered"
• "Complete"

I want to make the default value a calculated value based on number of ordered items compared to the receieved items.

``````if [ordered] = 100 and [receieved] = 0 then the status should be "Waiting for delivery"

if [ordered] = 100 and [receieved] < [ordered] but > 0 then the status should be "Partly delivered"

if[receieved] = [ordered] then the status should be "Complete"
``````

I have tried the following but it does not seem to work (yes I suck at this):

``````=if([receieved]="0"),"Waiting for delivery",if(AND([received]<[ordered],[received]>0),"Partly delivered",if([received]=[ordered]),"Complete"
``````
• When you say it doesn't work, what do you mean? Is there an error message or are you just getting an incorrect value? Nov 26, 2013 at 13:18

My understanding is that you have three columns, one for the quantity ordered, another for the quantity delivered, and a third for the status. Each row in the list represents a single order, so may have 3 ordered, 2 delivered and a status of Partly Delivered.

In your Status column, instead of using a calculated default column, make the column type Calculated, and use this formula:

``````=IF(AND([ordered]>0,[received]=0),"Waiting for delivery",
"Complete"))
``````

(You'll need to remove the carriage returns, they're just to make this easier to copy)

Whenever you update the item this will recalculate, whereas a calculated default value on a text data type will only update on creation.

• Thanks, I don't know why I didn't go with "your" solution for starters. And the formula worked like a charm!
– Skon
Nov 26, 2013 at 14:28
• No worries. SP uses pretty much the same syntax as Excel for formulas if that helps. Nov 26, 2013 at 14:44