Frank .S

asked on

Hi Experts, Id like a solution to solve the following error i have in my formula

FORMULA

[qty*1.1rnd0.3/4.2rnd1] /4.2

What Im wanting to do here is, whatever qty i add, i need the formula to add 10%, then round up to the next increment of 0.30, then i want it to divide by 4.20 and round up to the next full 4.20, the result to have 4.2 shown

If I enter the qty as 1.80 and have the formula work correctly, it should give me the following result 1/ 4.20

Here are the calculation steps that should be taken

1.80x1.1=1.98

1.98 roundup into increments of 0.3 = 2.10

2.10 / 4.20 = 0.50

0.50 roundup into increments of 4.20 = 4.20

final result = 1/ 4.20

FORMULA

[qty*1.1rnd0.3/4.2rnd1] /4.2

What Im wanting to do here is, whatever qty i add, i need the formula to add 10%, then round up to the next increment of 0.30, then i want it to divide by 4.20 and round up to the next full 4.20, the result to have 4.2 shown

If I enter the qty as 1.80 and have the formula work correctly, it should give me the following result 1/ 4.20

Here are the calculation steps that should be taken

1.80x1.1=1.98

1.98 roundup into increments of 0.3 = 2.10

2.10 / 4.20 = 0.50

0.50 roundup into increments of 4.20 = 4.20

final result = 1/ 4.20

Last Comment

I'm assuming that you need to prefix the numeric result with the text "1/"

="1/" & TEXT(ROUNDUP((ROUNDUP(A1*1.1/0.3,0)*0.3)/4.2,0)*4.2,"#,##0.00")

Before adding the prefix, it's the same as Saqibh's approach, except that I rounded up to the nearest 0.30 rather than 0.33333333

="1/" & TEXT(ROUNDUP((ROUNDUP(A1*1

Before adding the prefix, it's the same as Saqibh's approach, except that I rounded up to the nearest 0.30 rather than 0.33333333

Hi Brad, I thought mine rounds to 0.30, how do you say it rounds to 0.33333?

Saqib

Saqib

...deleted

Saqib,

You are correct. I was wrong about how the ROUNDUP function is working.

Brad

You are correct. I was wrong about how the ROUNDUP function is working.

Brad

You could use the MROUND function or the CEILING function.

MROUND rounds to the nearest multiple specified

CEILING rounds up to the next multiple specified.

Thanks

Rob H

MROUND rounds to the nearest multiple specified

CEILING rounds up to the next multiple specified.

Thanks

Rob H

With quantity in A2, use formula:

=TEXT("1/ "&CEILING(CEILING(A2*1.1,0.3)/4.2,4.2),"0.00")

Thanks

=TEXT("1/ "&CEILING(CEILING(A2*1.1,0

Thanks

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Or

="1/ "&TEXT(CEILING(CEILING(A2*1.1,0.3)/4.2,4.2),"0.00")

Thanks

Rob H

="1/ "&TEXT(CEILING(CEILING(A2*

Thanks

Rob H

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

=ROUNDUP(ROUNDUP(A1*1.1/3,