Forum Discussion
Round up / down in calculated column
Hi I am trying to use a calculated column to round up or downs cash figures. I can achieve this easily in excel, I thought the formula would be similar just formatted to work with list column...
To achieve rounding up or down cash figures in a calculated column in SharePoint, you can utilize SharePoint's built-in functions to perform the necessary calculations and rounding. Here's an example of how you can create the calculated columns 'voucher rounding' and 'cash rounding' based on your requirements:
Open the list or library where you want to add the calculated columns.
Navigate to the settings for the list and click on "List Settings."
Under the "Columns" section, click on "Create column" to add a new calculated column.
Provide a name for the column, such as 'Voucher Rounding'.
In the "Type of information" section, select "Calculated (calculation based on other columns)".
In the "Formula" box, enter the following formula:
plaintext
Copy code
=IF(MOD(Total,5)<=2.5, ROUND(Total/3,0)*2, ROUND(Total/3,0)*2+5)
This formula calculates the 'voucher rounding' by dividing the 'Total' by 3, rounding it to the nearest whole number, and then multiplying it by 2. If the remainder of the 'Total' divided by 5 is less than or equal to 2.5, it rounds down. Otherwise, it rounds up to the nearest multiple of 5.
Select the appropriate data type and format for the column.
Click "OK" to create the 'Voucher Rounding' column.
Open the list or library where you want to add the calculated columns.
Navigate to the settings for the list and click on "List Settings."
Under the "Columns" section, click on "Create column" to add a new calculated column.
Provide a name for the column, such as 'Voucher Rounding'.
In the "Type of information" section, select "Calculated (calculation based on other columns)".
In the "Formula" box, enter the following formula:
plaintext
Copy code
=IF(MOD(Total,5)<=2.5, ROUND(Total/3,0)*2, ROUND(Total/3,0)*2+5)
This formula calculates the 'voucher rounding' by dividing the 'Total' by 3, rounding it to the nearest whole number, and then multiplying it by 2. If the remainder of the 'Total' divided by 5 is less than or equal to 2.5, it rounds down. Otherwise, it rounds up to the nearest multiple of 5.
Select the appropriate data type and format for the column.
Click "OK" to create the 'Voucher Rounding' column.
Thank you for this - I think something similar to this will work for the cash rounding column, but not for the voucher rounding column and vouchers are only provided in multiples of 5, not the next whole number...
Here is what it's doing currently compared to the excel version
- ok, to desired rounding behavior for both the cash rounding and voucher rounding columns in your list, you may need to use a combination of calculated columns and custom formulas. While Excel provides more flexibility in this regard, you can still approximate the functionality in SharePoint by following these steps:
Create a new calculated column for the cash rounding. Let's call it "Cash Rounding."
Use the following formula in the calculated column:
=IF(MOD([Total], 1) > 0.5, ROUNDUP([Total], 0), ROUNDDOWN([Total], 0))
This formula checks if the decimal part of the [Total] value is greater than 0.5. If it is, the [Total] value is rounded up using the ROUNDUP function; otherwise, it is rounded down using the ROUNDDOWN function.
Create another calculated column for the voucher rounding. Let's name it "Voucher Rounding."
Use the following formula in the calculated column:
=ROUND([Total] * (2/3), 0) * 5
This formula multiplies the [Total] value by 2/3 to obtain two-thirds of the total. The result is then rounded to the nearest whole number using the ROUND function. Finally, the rounded value is multiplied by 5 to ensure that it aligns with the voucher requirement of multiples of 5.
By implementing these calculated columns, you can approximate the rounding behavior you described in the Excel version. However, it's important to note that SharePoint's calculated columns have limitations compared to Excel's formulas, and the precision of calculations may vary. It's recommended to thoroughly test and validate the results to ensure they meet your requirements.
Also, keep in mind that SharePoint lists are not as flexible as Excel in terms of complex calculations, and you may encounter some limitations when trying to replicate certain functionalities.- oh, so sorry about the result.