Excel formula, again

[bcr123]

Hello all.

If someone could please help me with the following formula.
I'm using MS Excel, Office 2003.

Lowest value for I30 is 1.

Arguments that are important, formula is in J30:

If I30 < or = 1.6 than J30 is 1;
If I30 > 1.6 than J30 is 2;

If I30 < or = 2.6 than J30 is 2;
If I30 > 2.6 than J30 is 3;

If I30 < or = 3.6 than J30 is 3;
If I30 > 3.6 than J30 is 4;

and all the way up, in increments of 1, with last argument being:

If I30 < or = 14.6 than J30 is 14;
If I30 > 14.6 than J30 is 15;
---------------------
Real example:

I30 = 12.67, J30 should have value of 13.
I30 = 17.11. J30 should have value of 15.
-------------------

Thank you for your time and help.

[Stewart Ross]

You should be looking for the common pattern and generalising the result. The individual numbers are all treated in the same way - rounded down if below n.61, rounded up if above.

All you need to do is to use the normal rounding formula - which rounds up on decimal values above the 0.5 mark and down on those below - on the value in I30, but less 0.11 (to set the rounding threshold to 0.61 instead of 0.5):

In J30 place the simple formula

=ROUND(I30-0.11, 0)

Test data

Value	Result
 1.0     1.0
 1.5     1.0
 1.6     1.0
 1.7     2.0
 2.0     2.0
 2.5     2.0
 2.6     2.0
 2.7     3.0
12.5    12.0
13.0    13.0
13.5    13.0
13.6    13.0
13.7    14.0

You will need to limit the final value to 14, which I have not done above. I leave that to you as an exercise. It can be done by wrapping the formula above in an IF statement, but in case this is some kind of homework exercise (which, for obvious reasons, we cannot do on your behalf) I leave this last part to you.

-Stewart

[bcr123]

Thank you Stewart.
Appreciated.

[bcr123]

My knowledge of Excel is very limited.
This is not homework, it is some exercise that I am running, trying to learn some Excel functions and automate some of my work.

This is what I came up with for J30 & it is not working:

=ROUND($I76-0.11,0),IF($I76 >15,15)

Thank you for your time and kind consideration.

[NeoPa]

I would use ROUNDUP() myself, as catching the point after .6 is difficult with ROUND(). Unfortunately it rounds to -1 when the value is less than 0.6.

=IF(ABS(I30)<0.6,0,ROUNDUP(I30-0.6,0))

[NeoPa]

Ah. 15 is a maximum huh. That may have been worth specifying specifically.

Try instead :

=MIN(IF(ABS(I30)<0.6,0,ROUNDUP(I30-0.6,0)),15)

[bcr123]

Thanks, NeoPa, as always!

Minimum value in I30 is 1, so this is working fine.

Having in mind line that you have posted - What I need to type to have J30 at 15 for all values in I30 that may go from 14.61 up.

(Example, if I30 is 17.66, I ould like J30 to be 15.)

Thank you for your time.

[bcr123]

Thanks NeoPa!

I thought that this was ok, as posted in my original post.

Sorry for the confusion and thank you very much for your time!

(From original post)

If I30 < or = 14.6 than J30 is 14;
If I30 > 14.6 than J30 is 15;
---------------------
Real example:

I30 = 12.67, J30 should have value of 13.
I30 = 17.11. J30 should have value of 15.

[NeoPa]

Reading what you posted, and the way it was explained, I guessed it was likely that this was simply a mistake. A typo or whatever. We are not short of members who rush questions off with little care. Trust me. You get used to trying to guess what people are actually trying to say after a while. For a point as important as this I would certainly state it explicitly.

Even if I had known to treat it more seriously, there could be some who didn't even notice the point.

PS. Just as I seem to have missed the point you did state explicitly (I30>=1) :(
TBH I did notice it before, then just forgot by the time I came to offer the solution.

Try this instead :

=MIN(ROUNDUP(I30-0.6,0),15)

[bcr123]

Thanks NeoPa!

It is truly appreciated.

I understand what you say about clarity of questions, especially as there is massive difference in ways senior (in terms of number of posts & experience) members of forums (in general) communicate when compared to newbie.

Thank you, as always, for your time and help.

All the best!

[NeoPa]

It's always a pleasure dealing with members who have such good manners :)