 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008IF formula to round up values depending upon their outcome
I am using an IF formula to calculate between two cells, one is J (width) the other is K (length). Currently these formulas give an answer that then has to be rounded up based on the decimal place. I need the formula to also round up the amount to quarter increments. For example if the answer is 1.17 then the formula needs to make it 1.25, if it is 1.33 then the formula needs to make it 1.5, and finally if it is 1.63 then the formula needs to make it 1.75. So how do I add or make the formula round up to quarter increments? The formula that I am using is: =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) Tty: =IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I am using an IF formula to calculate between two cells, one is J (width) >the [quoted text clipped - 10 lines] > The formula that I am using is: > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) Thank you so much this has solved a question that has been around for quite sometime. Could you answer another formula for me. I have two cells that I need to sum up, one cell has a set value (E), and the second cell is a value that is only entered when needed (F). How do I sum up the two only when F has a value in it? (If there is no value in (F) I want the formula to not place (E)'s value as the sum). > Tty: > [quoted text clipped - 14 lines] > > The formula that I am using is: > > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) Try: =IF(F41="","",SUM(E41:F41)) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Thank you so much this has solved a question that has been around for > quite [quoted text clipped - 29 lines] >> > The formula that I am using is: >> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) Here's another option for your first question..... =CEILING(K41/INT(7-J41),0.25) > Try: > [quoted text clipped - 33 lines] > >> > The formula that I am using is: > >> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) Sorry, that would need to be.... =CEILING(K41/MIN(4,MAX(2,INT(7-J41))),0.25) > Here's another option for your first question..... > [quoted text clipped - 37 lines] > > >> > The formula that I am using is: > > >> > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) Hi Sandy, You might want to simplify the formula below to read: =CEILING(K1/IF(J1<=3,4,IF(J1<=4,3,IF(J1>4,2))),0.25) Cheers, Shane Devenshire Microsoft Excel MVP > Tty: > [quoted text clipped - 15 lines] >> The formula that I am using is: >> =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) Hi again, I see you got a shorter formula already, that one can also be shortened to: =CEILING(K1/MIN(4,MAX(2,7-J1)),0.25) Cheers, Shane Devenshire Microsoft Excel MVP > I am using an IF formula to calculate between two cells, one is J (width) > the [quoted text clipped - 10 lines] > The formula that I am using is: > =IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2))) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.