By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,474 Members | 1,522 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,474 IT Pros & Developers. It's quick & easy.

Excel formula, again

P: 47
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.
May 19 '09 #1
Share this Question
Share on Google+
10 Replies


Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. =ROUND(I30-0.11, 0)
Test data
Expand|Select|Wrap|Line Numbers
  1. Value    Result
  2.  1.0     1.0
  3.  1.5     1.0
  4.  1.6     1.0
  5.  1.7     2.0
  6.  2.0     2.0
  7.  2.5     2.0
  8.  2.6     2.0
  9.  2.7     3.0
  10. 12.5    12.0
  11. 13.0    13.0
  12. 13.5    13.0
  13. 13.6    13.0
  14. 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
May 19 '09 #2

P: 47
Thank you Stewart.
Appreciated.
May 19 '09 #3

P: 47
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.
May 19 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Expand|Select|Wrap|Line Numbers
  1. =IF(ABS(I30)<0.6,0,ROUNDUP(I30-0.6,0))
May 19 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
Ah. 15 is a maximum huh. That may have been worth specifying specifically.

Try instead :
Expand|Select|Wrap|Line Numbers
  1. =MIN(IF(ABS(I30)<0.6,0,ROUNDUP(I30-0.6,0)),15)
May 19 '09 #6

P: 47
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.
May 19 '09 #7

P: 47
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.
May 19 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. =MIN(ROUNDUP(I30-0.6,0),15)
May 19 '09 #9

P: 47
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!
May 19 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
It's always a pleasure dealing with members who have such good manners :)
May 19 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.