I need serious help with this formula regarding rounding a number please!!!

J

Jaffabfc

Board Regular
Joined
Jul 5, 2013
Messages
196
Office Version
  1. 365
Platform
  1. Windows
Hi,

Right i have a spreadsheet and in column j2 i have a formula of the following

"=l2/365*e2*2" which brings back the answer of 637. What i am wanting this to do is round up to 640, i can do this with no problem with the round formula.

What i want is that if the formula above equals say 2832 i want to be able to round this to 2800 and 2830, i want to be able to enter a formula and just drag it down.

I have in another cell the following "=IF(LEN(J2)=3,ROUND(J2,-1),IF(LEN(J2)=2,ROUND(J2,-1)))" which works if the answer in j2 is wrote it and not worked out by a formula.


How can i do it all in 1 cell and drag down (preferably j2 and drag down)

I just cant work it out.

thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hi,

I think your problem is that both parts are set to Round to -1, regardless of length. It should work off of formula results as well as typed values.

I think below should work for what you want.

Code: 
=ROUND(J2,(LEN(J2)-2)*-1)

Hope this helps,

Eric.
 
Upvote 0
hi,

This doesnt seem to work, i get the answer of 0

:(

Thanks for trying though
 
Upvote 0
Is perhaps this what are you looking for:

Code: 
=IF(LEN(L2/365*E2*2)=3,ROUND(L2/365*E2*2,-1),IF(LEN(L2/365*E2*2)=2,ROUND(L2/365*E2*2,-1)))
 
Upvote 0
Hi Rent32

That looks like it is a bit closer but i am getting the error of false now :(

Also changed the L2 in the formula to I2, sorry must have been my writing in the first post.
 
Last edited:
Upvote 0
Try:

Code: 
=IF(LEN(I2/365*E2*2)=3,ROUND(I2/365*E2*2,-1),IF(LEN(I2/365*E2*2)=2,ROUND(I2/365*E2*2,-1))*IF(LEN(I2/365*E2*2)=4,ROUND(I2/365*E2*2,-2)))

If it is not what you want, post an example. Your first post it is not very clear.
 
Upvote 0
Hi,

Is the problem then perhaps that "=l2/365*e2*2" does not equal exactly 637 and it is merely formatted to no decimals.

What is in l2 & e2?

Please post sample data & expected results.

Eric
 
Upvote 0
Eric Golf said:
Hi,

Is the problem then perhaps that "=l2/365*e2*2" does not equal exactly 637 and it is merely formatted to no decimals.

What is in l2 & e2?

Please post sample data & expected results.

Eric
Click to expand...
This is correct, i have changed the format of the cell

and how do i upload an attachment?
 
Upvote 0
i want column j to say 640/350/380 i have a few 100 more that go upto 4 digits too
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Formula issue. Rounding number up.
Replies
4
Views
452
Fluff
Fluff
O
  • Question
Excel formula / vba to calculate the Time Taken according to batches of data
Replies
1
Views
435
BigBeachBananas
B
G
  • Question
Need help with a formula regarding dates, money, ifs, among other things
Replies
0
Views
225
Gonsa17
G
J
  • Solved
Drag down formula in row an increment value by more than 1
Replies
6
Views
948
StephenCrump
S
V
  • Solved
Make 1 list of names out of multiple using VBA
2
Replies
10
Views
858
VikingLink
V

Forum statistics

Threads
1,214,864
Messages
6,121,986
Members
449,058
Latest member
oculus

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top