I am using the round up function as follows =ROUNDUP(D22,G3)>3 but it only displays false or true in the chosen cell. D3 is the digits or decimal places box. what do I need to add in order to display the figures greater than the no 3 in the designated cell


Basically i missed the part I only wish to ROUNDUP if the cell is greater than the no 3
Thanking you in advance

Not sure this is entirely clear.

You want to Roundup only if a cell is greater than 3?

What do you want if the cell is less than 3?

=IF(A1>3,ROUNDUP(B1,0),B1)

Thanks for the reply and sorry for my poor explanation. Basically I have equipment that comes in set lengths apart from the first three metres. The lengths are as follows 0.5 metre 1 metre 1.5 metre 2 metre 2.5 metre 3 metre all the others go up in increments of one meter until a point and then go up in two meter increments. So everything below the figure three goes up from 0 to 3 in 0.5 increments that's why I asked for greater than 3 as I was going to tackle the below figures separately being a novice however if you can include the below and above figures in the one equation I would be knocked over thanking you in advance

Could you piece together a file with some example data and the results you expect?

Please find attached hope it makes sense

Attached Files

EXCEL LENGHT CALC EXAMPLE.xlsx (10.2 KB, 13 views)

For the life of me, I can't get the first part (3 and under) with using one of the round functions, but this should do it...

Let me know how this works out for you...

B1 copied down =IF(A1<=3,LOOKUP(A1,{0,0.5,1,1.5,2,2.5,3},{0.5,1,1 .5,2,2.5,3,3}),ROUNDUP(A1,0))

Are the value 3 and below perfect values in the .5 increments or could they be 2.6 and such?

You can also try =CEILING(A1+0.0000000000001,0.5+0.5*(A1>=3))

the parenthesis around A1>=3 are crucial

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The below no 3 values can come out from a previous calculation at anything. I t can come out at say 2.32 mtrs however the only size available to work safely is the next one up 2.5 mtrs so it will round up to 2.5 mtrs if greater than two.

Well looks like it solved they both work out great cant thank you enough for taking the time to help . Got to test it through the range of results but looking good so far.

Spoke too soon ? They both work to a point. IF the figure comes out at 1.5 it should display 1.5 as this is a round up length. However when it runs in the statement
=IF(A1<=3,LOOKUP(A1,{0,0.5,1,1.5,2,2.5,3},{0.5,1,1 .5,2,2.5,3,3}),ROUNDUP(A1,0)) if the return figure is 1.5 it rounds up to the figure 2 where as it should display 1.5 any ideas as to the solution

=CEILING(A1+0.0000000000001,0.5+0.5*(A1>=3)) returns the same set of figures however once you get a result say 3 it rounds up to four however it should display 3 as it is a round up figure

Nearly solved

Try
=CEILING(A1,0.5+0.5*(A1>=3))

Adding a sheet with expected results ( not just explanation) makes things easier

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

It works perfectly now. Thank you both for taking the time solving this problem for me. I will take on board your advice attaching a sheet would make it easier to understand at the other end. However I have to say you have gone beyond what some people would have done and taken the time to understand something which at the other end must have been difficult to understand.
Most people would just have had a look and said mmm move on.
Most people who are good at what they do lack the professionalism to pass their knowledge on. However you two solved that one as well thanking you both

You are most welcome. We are happy to help.

Now the final problem no matter how hard I try the drop down box does not give me the option to display problem solved guidance would be good or am I just missing or cant see the marked as solved bit lol

I don't think this forum has that option. Eventually I believe a moderator may mark "as solved".