Calculated value formula

Question

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"

Answer 1

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",
  IF(AND([ordered]>0,[received]>0,[received]<[ordered]),"Partly delivered",
   "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.