1

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? – CLockeWork Nov 26 '13 at 13:18
2

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.

  • 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 '13 at 14:28
  • No worries. SP uses pretty much the same syntax as Excel for formulas if that helps. – CLockeWork Nov 26 '13 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.