# FORMULA ASSISTANCE

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
Microsoft Excel

Last Comment
Rob Henson
Saqib Husain

If A1 contains the qty then enter this formula in another cell and it will give you the desired result (I hope)

=ROUNDUP(ROUNDUP(A1*1.1/3,1)*3/42,1)*42/4.2
byundt

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
Saqib Husain

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

Saqib
terencino

...deleted
byundt

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

Rob Henson

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
Rob Henson

With quantity in A2, use formula:

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

Thanks
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
Rob Henson

Or

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

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