Forum Discussion
Solved
SharePoint Calculated List Column
Under a standard custom list, can I use the calculated field to assign values to a choice field that has words not numbers? We want to determine cost for printing in black and white vs color. I have a choice for black & White vs Color. I would like to make Black .11 and Color .19 and multiply it by the quantity
- You can use this query written for you.
=IF([Pagetype]="Black & White",([page no]*11),IF([Pagetype]]="Color",[page no]*19))
- You can use this query written for you.
=IF([Pagetype]="Black & White",([page no]*11),IF([Pagetype]]="Color",[page no]*19))- Thank you so much Santhosh! There are some other ones I have to create for other lists like shipping but now I understand the formula on how to do them. You help was extremely appreciated! Also thank you to everyone else that helps me with this. Had trouble finding forums online for this formula.
- Yes, but bear in mind there is a limit to the number of if statements you can use (i think it is 7) in a single calculated field. So if you have more than 7 colors, you would have to combine multiple calculated fields to achieve what you want.
You might be better off to use a lookup field, where the lookup value is the "color" field, and then the lookup list also has a field for the value. Then maybe your calculation is a SPD workflow, instead of an actual calculated field. Hello,
Calculated columns in a basic list will provide a result of some sort that is displayed in your view based on the calculations you do with existing data. So if you want to do math then it would be best to capture numbers in your other columns first.
- Example:
- Black (Number Column)
- Color (Number Column)
- Calc (Calculated Column)
- Formula: =[Black]*[Color]
so in your view using your example numbers you would see something like this:
Black Color Calc 11 19 209 Also remember you can add counters to the view (See modify view > totals section) give you that information if need be.
Otherwise you cant use a calculated field to change your Choice Field options. Conditional logic can be developed in InfoPath.
- Maybe I can explain it a little better. I have a list already created. I have a field with a dropdown for ink color that has "Black & White" and "Color" as an option. I also have a single field called paper quantity. Black & White costs 11 cents a side and color costs 19 cents a side. I was hoping I could have Black & White represent .11 and Color represent .19 and then times it by the quantity category and have it output in the form as a total estimated cost. Does that sound like it would work? Let me know what you think and thanks for responding!
- Hi Brian
Assuming that by dropdown field, you mean that you are using a Choice column and that it has the two options "Black & White", and "Color".
If that is so, you calculated column, should be able to have a formular like:
=[Quantity] * IF([Ink color] EQ "Black & White", 0.11, 0.19)
Above is not the real code :) just an example
Enjoy, John
