Create a roundup for a figure when I am using a IF function for something else.

[bluedusty]

Hello,

I am wondering how I go about using the round formula when I am already using and IF function in a cell for another reason.

Currently I have this formula in place:
=IF($J$3="",SUM(D5:D28),IF($J$3="23 Hour Day",SUM(D5:D27),IF($J$3="24 Hour Day",SUM(D5:D28),IF($J$3="25 Hour Day",SUM(D5:D29))))) - I need to provide hourly information so have it set to add or remove a box depending on time stamp selected. D5:D28/9 will normally be a long figure for example 0.299583333333

What I need to do is return the value in cells D5:D28/29 (Depending on IF function) to 3 decimal places. Cells D5:D28 display 2 decimal place but could make that 3 if it helps.

When I try to add the round function in it just seems to return a true, false or spill comment not an acutal figure.

Hopefully I have made what I am after clear enough, All help much appreciated.

Thanks

 

[Fluff]

How about

=ROUND(IF($J$3="",SUM(D5:D28),IF($J$3="23 Hour Day",SUM(D5:D27),IF($J$3="24 Hour Day",SUM(D5:D28),IF($J$3="25 Hour Day",SUM(D5:D29))))),3)

 

[bluedusty]

Hi Fluff,

This seems to accept the formula but if I put figures into cells D5:D29 it does not change from 0.00

the 0.14 is actually .1354166666667 but the sum of rates where this formula is doen not change.

Thanks for your help so far, very much appreciated

 

[Fluff]

Are you sure that D5:D29 are numbers & not text?
Also what is in J3?

 

[bluedusty]

D5:D29 are numbers as per the box above showing the reading of 0.14

J3 is a list that you either pick 23hr day, 24 hr day or 25hr day.

If j3 is selected to 24hr day it will only look at figures in D5:28 and if j3 is on 23hr day it only looks at D5:D27 etc.

Thanks

 

[bluedusty]

Sorry Fluff, Thank you very much, that formula did work - Operator error doh

 

[Fluff]

Glad you sorted it & thanks for the feedback.