Rounding to Multiples WITH IF's

**SCCCJ** · 04-20-2011, 03:19 PM

I'm trying to write a formula using a combination of the MROUND and IF functions. I can't get it to work. Basically here's the scenario: I've got a formula that's already working to calculate into a cell (for example: B4). I plan on hiding that column (hypothetically B) of cells and put this MROUND formula into, say, H4.

Ultimately, I want H4 to take the results of the formula in B4 and round them the nearest multiple...

BUT there are conditions.... Different "multiples" depending on how large the number is:

Round to 3's - if the result is 1 to 18
Round to 6's - if the result is 19 to 36
Round to 12's - if the result is greater than 37

I also need to add an "IF" statement to the effect that if the result in B4 is "0" that H4 will reflect a hyphen only "-" rather than a zero.

Any help would be appreciated! Thank you

**NBVC** · 04-20-2011, 03:24 PM

Try:

=IF(B4=0,"-",MROUND(B4,LOOKUP(B4,{1,19,37},{3,6,12})))

**SCCCJ** · 04-21-2011, 12:21 PM

It works great, but i'm running into problems with #N/A popping up.

**NBVC** · 04-21-2011, 12:24 PM

Can you give examples?

**SCCCJ** · 04-21-2011, 12:37 PM

27 87 0 0 0 30 96 #N/A #N/A #N/A
36 111 0 0 0 36 120 #N/A #N/A #N/A
27 87 0 0 0 30 96 #N/A #N/A #N/A

The black numbers are those that are "rounding" the corresponding number in pink. In each line the 1st number in pink corresponds with the 1st number in black. As you can see, "#N/A" pops up for the zeros which I find to be very bizarre, since this worked fine yesterday. Only when i opened the spreadsheet this morning did this happen.

**NBVC** · 04-21-2011, 12:51 PM

Could those 0's be actually something between 0 and 1 (i.e. decimal value, like 0.00001)?

If so you can try either rounding in the formula like:

=IF(ROUND(A1,0)=0,"-",MROUND(A1,LOOKUP(A1,{1,19,37},{3,6,12})))

or changing the first Lookup array value to 0...

=IF(A1=0,"-",MROUND(A1,LOOKUP(A1,{0,19,37},{3,6,12})))