# Round Function within IF Function

Microsoft Excel 2007
July 28, 2009 at 05:04:28
Specs: Windows Vista
 I have the following IF Function and need to be able to use the ROUND Function within this to round the figures to the nearest \$10. How can this be done?=IF(B12:B22>\$B\$7,B12:B22*\$B\$6,0)

See More: Round Function within IF Function

#1
July 28, 2009 at 12:59:49
 I'm not familiar with your use of IF over an array or cells.Please explain how this formula works and I'll try to help with the rounding issue.Normally, I would just say to use:=IF(B12:B22>\$B\$7,MROUND(B12:B22*\$B\$6,10),0)but since I don't understand what you are trying to do with the IF statement, I don't know if that will work.

Report •

#2
July 28, 2009 at 16:09:38
 Hi,I have a spreadsheet containing Sales Figures and bonus payments. I want to write a statement which says if the sales figure is above a certain amount then calculate a 10% bonus, if not then apply \$0. I then want to round the bonus amount to the nearest \$10 eg if the bonus is \$383 then I want to round it to \$380Hope this helps

Report •

#3
July 29, 2009 at 01:14:38
 Well the easiest thing to do is say:=ROUND(result,-1)This will change 383 to 380 as you require. The "result" part of the formula can be anything: another formula, a cell reference, etc etc.I don't really understand what the arrays are for so I'm assuming you're summing them...as a result I'd say that the formula you want to use is therefore:`=ROUND(IF(SUM(B12:B22)>\$B\$7,SUM(B12:B22)*\$B\$6,0),-1)`

Report •

Related Solutions

#4
July 29, 2009 at 04:57:07
 Thanks All, I have now solved my problem

Report •

#5
July 29, 2009 at 07:05:28
 I'd still like an explanation of your original formula.=IF(B12:B22>\$B\$7,B12:B22*\$B\$6,0)Not what you are trying to do with it as far as assigning a bonus, but the Excel mechanics behind it. It appears to be an array formula, but I don't see how it helps accomplish your goal.It would also be nice if you posted your solution in this thread so that it gets saved in the archives. Someone might search for "rounding" and your solution might help them.

Report •

#6
July 30, 2009 at 03:02:40
 I used the following formula which appears to work=ROUND(IF(\$B\$10:\$B\$18>\$B\$7,\$B\$10:\$B\$18*\$B\$6,0),-1)

Report •