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 names instead of cells, but it doesn't work.
I have a 'Totals' column that adds up all the cash figures based on other columns in the list.
What I need is two more columns, in my image they are 'voucher rounding' and 'cash rounding', They need to divide the 'Total' into one third for in the 'cash rounding' column, and two thirds in the 'voucher rounding' column, and if the 'Total' figure is an odd number, the 'cash rounding' should be rounded up to the next £1, and the 'voucher rounding' column round up or down to the nearest multiple of 5.
I hope this makes sense
LukeSykes a seemingly simple request but actually very difficult to deliver and probably not possible in SharePoint alone. I think I can do it with a flow in Power Automate that's triggered by a JSON formatted button in the list, and I've got most of the way there. The bit I am slightly struggling with is the requirement to round up or down to the nearest 5 for the voucher rounding column. I'll look at this again over the weekend and hope to work out a solution for you.
Rob
Los Gallardos
Microsoft Power Automate Community Super UserThats fantastic - thank you
Vouchers come in multiples of £5, so a £5 voucher, £10, £15, £20 and £25, so if the calculation comes to £9 is rounds up to £10, if it comes to £6 it rounds it back down to £5, however, if it comes to in the middle, like £23, then it would round back down to £20 not up to £25
I actually already have a flow that creates the list item so if it could be integrated into that that would be superb.
- 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.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.
