Forum Discussion
Excel Formula
Hi
I have a current formula that has a ROUNDDOWN based on a SUM
=ROUNDDOWN(SUM((F37/$D$24)*G37*H37*I37),0)
What I wanted to know is if it was possible to combine round formula's (or add an IF?) to this.
Basically we want to keep the round down if the result of the SUM is 1.1 and higher but if the result of the SUM is < 0.9 then we want to have this round up the result to 1.
I've tried lots of forums and excel help sites but I couldn't find anything so hoping a forum guru might be able to assist.
Thank you! Sonia
4 Replies
- Thank you John!! That's perfect! I have never use MAX before! I have 1 small issue though - the default in the spreadsheet now is 1 because the formula currently creates a 0 result and this now rounds up to 1. Is there any sneaky way that I can use the same formula but keep the 'default' to 0 until the other cells are completed and then change the result as needed? I'm pretty sure there isn't but thought I would ask. Thank you again for taking the time to reply! Really do appreciate it!
With an If you can do it...
=IF(F37*G37*H37*I37,MAX(1,INT(F37*G37*H37*I37/$D$24)),0)
=IF(PRODUCT(F37:I37),MAX(1,INT(PRODUCT(F37:I37)/$D$24)),0)
Blessings!
Thank you John! That works perfectly!! Really appreciate your time / help with this!
Hi, Sonia Tesolin!
You could try this formula:
=MAX(1,INT(F37*G37*H37*I37/$D$24))
or
=MAX(1,INT(PRODUCT(F37:I37)/$D$24))
Blessings!
