Calc formula to even to particular number

English
,
#1

Is there a formula in LO Calc to even number to particular value. I have payments which are close to original value subtracted by some CC commissions so I would have Column B like

971.23
983.67
975.50
980
973.22

and I need to calculate VAT based on invoice value which is in this case 980 so I want to multiply B1*VAT% …
I know I could do an if function, but I have 4 or 5 different values that needs to be rounded and they are not rounded like 1000 or 100. Technically I could do some ranges like if value +- 10 of 980 then 980, else if +-10 660 then 660 but just if statement would get very long

#2

Hi, try
=ROUND(A1/20,0)*20

1 Like
#3

it helps for some, but for example 964 than rounds to 960. Also I have one of the values 375 (which I could do `=ROUND(B1/25, 0)*25.

#4

I guess I can find a couple different factors like for 375 =ROUND(B1/75,0)*75 FOR 980 =ROUND(B1/49,0)*49

#5

For ease of use, lets say you have a named range for VAT so it can be used in a formula and your number is in cell A1.
To add VAT to a value in A1 the formula is =A1*(1+VAT)
To calculate the VAT exclusive amount of a value in A1 the formula is =A1/(1+VAT)
To calculate the amount of VAT in a value in A1 the formula is =A1-A1/(1+VAT)
VATCalculate.ods (13.0 KB)

Replace VAT by commission percentage in the formula to calculate commission.

I don’t really understand the rest of your question. An example spreadsheet would help

1 Like
#6

Problem is with different rounding should be provided in particular cells (Column B). I’ve updated spread sheet so you can see. Actual payments don’t have exact commission, as I have different gateways, and they also charge different commission for individual Credit Cards, but since I don’t have many different products, I can assume to which product each payment belongs to even though it’s not exactly as on Invoice. But VAT is calculated based on the issued invoice, so I’m trying to come out with formula that would automatically calculate which product is which based on payment amount.
VATCalculate.ods (16.5 KB)

#7

I would use VLOOKUP to search for nearest value, cells coloured green.

  • The range to look up must be sorted in ascending order, in attached spreadsheet I have coloured the range yellow.
  • To allow for commissions, I multiplied the Payment by 1.05 which seems to be maximum commission, change the 5% (cell colour pink) to a different number if you wish but the Payment plus commission must always be equal to or greater than Invoice.

What happens if a customer orders two of Product D? The total would be the same as a single purchase of Product B. You should use a reference number for the invoice.

VAT
I think the Invoice total must always include VAT. Your VAT calculation is too high, if your invoice was EUR 1220 then the VAT component would be EUR 220 but your calculations give EUR 268.40.
I have corrected your VAT calculations using the formula I gave in my first comment and coloured the range blue. Corrected example above of VAT shown with red characters.
Cheers, Al
VATCalculate87402EA.ods (21.4 KB)

1 Like
#8

Thanks that solves my issue. I didn’t realize VLOOKUP will look for values bigger than. Anyways, good solution. I have customers only buy 1 product at the time, and prices are spread enough so it’s not a problem

#9

In fact searching in a sorted list for a value equal or less than is the default for VLOOKUP, see Spreadsheet Functions

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.