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)

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.

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 $380 Hope this helps

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)

Thanks All, I have now solved my problem

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.

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)