ROUNDDOWN function in IF formula.. ?

**jamieblue** · 06-02-2012, 07:13 AM

Hi, I'm new to these forums sorry and have a couple of questions about some stuff - hope they're not too annoyingly easy for you..

Anyway, I think the first one is probably easier. I've got this set of 12 initial possible values, quite simply 1-12. However each one must correspond with a different value. I was thinking of doing a nested IF? But I believe they only go up to 7 so didn't think it was possible.

I need 1 to have a corresponding value of 5, 2 -> 10, 3 -> 15.... in this sequence up until 8, but then 9 needs to be 50, 10 -> 60, 11 needs to be 75 and 12 -> 90..

I mean I could put the corresponding values in myself obviously, but this takes effort cause the inital values of 1-12 will always be changing..

The second thing is, once I (hopefully) have done this, I then times the corresponding value (i.e. 5, 10, 15) by a constant - but the problem is some of the new numbers generated need to be rounded down (actually changed, not formatted) by 0.5. i.e. the constant is 90, and if I use my equation I get 4.5 for 5 (I know complicated equation right? :P), 13.5 for 15 etc, that need to be rounded down to 4, 13 etc. But using the other values, obviously all even numbers except for 9, i get numbers with no decimal places, that I want to keep as they are. I've tried the IF function as well as ROUNDDOWN thing with it, but it seems hard with so many values. I tried some stuff with ISODD and ISEVEN with the nested IF with AND and ROUNDUP and stuff, but I wasn't sure how to 'keep' a value of a cell, like all those that are fine that don't have decimal places - but I really don't think I'm doing the right thing. Is this even possible what I'm asking?! I hope you can understand what I mean :P

Anyway, thanks in advance if anyone offers any support

**dilipandey** · 06-02-2012, 07:40 AM

Hi jamieblue,

Welcome to the forum.

Would suggest you to upload a sample workbook to explain your query. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**martindwilson** · 06-02-2012, 07:41 AM

=choose(a1,5,10,15,20,25,30,35,40,50,60,90)
=ROUNDDOWN(b1,0)
so
roundown(choose(a1,5,10,15,20,25,30,35,40,50,60,90)*your constant,0)

**npamcpp** · 06-02-2012, 07:42 AM

I think the first one is probably easier.

Put the values 1 to 12 into cells A1 to A12. Put their corresponding values into B1 to B12. In C1 enter a number between 1 and 12. In D1 use this formula to show the corresponding value:

=vlookup(c1,a1:b12,2,false)

If you always want to round DOWN, then you can use the INT() function

=INT(13.5) returns 13, so does INT(13.9)

You can wrap the Vlookup into an INT() to do both things in one formula

=INT(vlookup(c1,a1:b12,2,false)/90)

If that does not help, please upload a workbook and explain in context. Mock up the desired results manually and explain the rules.

**martindwilson** · 06-02-2012, 08:26 AM

if you do create a table as suggested use =lookup(c1,a1:b12) its just shorter

**jamieblue** · 06-02-2012, 09:24 AM

Thanks for the welcome dilipandey! People have been really helpful seems like a nice place.

Originally Posted by npamcpp

Put the values 1 to 12 into cells A1 to A12. Put their corresponding values into B1 to B12. In C1 enter a number between 1 and 12. In D1 use this formula to show the corresponding value:

=vlookup(c1,a1:b12,2,false)

If you always want to round DOWN, then you can use the INT() function

=INT(13.5) returns 13, so does INT(13.9)

You can wrap the Vlookup into an INT() to do both things in one formula

=INT(vlookup(c1,a1:b12,2,false)/90)

If that does not help, please upload a workbook and explain in context. Mock up the desired results manually and explain the rules.

This has worked PERFECTLY npamcpp! Thanks so much, exactly what I was looking for.

Next time if I have a problem though I'll upload a workbook or something, guess it would make things a lot clearer. But yeh, in the end I just created a separate lookup table that couldn't be seen, referenced all the cells, and the wrap function with the INT worked great with my formula