Multiple IF statements checking decimal values.- USED VLOOKUP INSTEAD OF NESTED IF

**97s** · 03-17-2012, 11:35 PM

Hello everyone,

First post here, but hoping this question is quite easy. I am using Excel 2010.

I have number decimal values that I need to round, for example:

Anything below 1.07 needs to be rounded down to 1.0
Anything above 1.07 needs to be rounded up to 1.25

Anything below 4.22 needs to be rounded to 4.25
Anything above 6.22 needs to be rounded up to 6.50

Anything below 7.37 needs to be rounded to 7.50
Anything above 8.37 needs to be rounded up to 8.75

Anything below 4.52 needs to be rounded to 4.50
Anything above 4.52 needs to be rounded up to 6.00 decimal value.

Basically I am rounding based on some quarter rules that I have established.

The tricky thing is, I understand how to setup the rounding in the IF function, the only problem is I don't understand how I can target only to check to round based on the decimals?

Also the other issue is how to round the integer in the last example.

Does anyone have any help on this subject? I would very much appreciate it. I looked through the threads and I couldn't find something similar to this, I hope I didn't miss it.

Thanks so much

Joseph Rosenblatt

**MarvinP** · 03-18-2012, 12:31 AM

Hi Joesph and welcome to the forum,

I don't see the logic in your rounding but you can create your own custom logic using a VLookup Table with the last argument of TRUE. See the attached for a sample. It isn't exactly what you want but close.

**97s** · 03-18-2012, 12:36 AM

Thanks for your quick reply. My rounding logic is bizarre, but it is what I have to go by for this office work. Can you explain the formula for a VLOOKUP. It appears to be like this:

VLOOKUP(Cell number to test, Base Number to compare it to, number to round it to, 2 decimal places?, TRUE because u always want to check?)

Nevermind found this:

http://office.microsoft.com/en-us/ex...005209335.aspx

**97s** · 03-18-2012, 12:40 AM

OK. I think I like the VLOOKUP. It works perfectly for what I need.

However my next question is, is there any way to build this into the cell formatting, so I dont have to use multiple columns etc? is there a way to just apply these rules to any data entered in a certain column?

Thanks again, Huge help so far.

-Joseph

**97s** · 03-18-2012, 12:57 AM

Actually I am having problems with the VLOOKUP, now that I have my data set in.

I wish I could just use if. I have the statement built and working, however it appears it breaks excel, I guess they dont support nesting this many conditions.

Yawn

**97s** · 03-18-2012, 01:13 AM

OK IGNORE THE ABOVE. I GOT THE VLOOKUP WORKING PROPERLY! Now I just wonder if there was any way where I could directly input data into a field and it uses my vlookup data?

**97s** · 03-18-2012, 01:20 AM

Just to be clear, I would like for a cell to automatically have this formatting rule:

=A27 - MOD(A27,1) + VLOOKUP((MOD(A27,1)),$H$11:$I$15,2,TRUE)

Basically it takes the number, gets the decimal, subtracts it from the number, then checks it against my decimal rounding rules, then adds the correct amount back to the number?

Thanks for all your help so far. If this isn't possible, just let me know, if it is , it would be so fantastic! If i have to have extra rows to compute the data, then that is fine.

**97s** · 03-18-2012, 01:44 AM

I am actually going to mark this as solved, and open another question relating to what my new problem is here : http://www.excelforum.com/excel-work...23#post2736523

Thanks for your help MarvinP